gv00rbNr

1DECLARE @UserID VARCHAR(100)
2SET @UserID = 'DOMAIN\USERNAME'  -- Windows Login you want to add
3 
4-- Get NAV version
5DECLARE @NavVersion AS INT;
6DECLARE @dbversionno AS INT;
7SELECT @dbversionno = databaseversionno
8    FROM dbo.[$ndo$dbproperty]
9    WHERE programid = 0;
10SET @NavVersion =
11    CASE
12        WHEN @dbversionno >= 70200 AND @dbversionno < 70720 THEN 60 --'2013'
13        WHEN @dbversionno >= 70720 AND @dbversionno < 71040 THEN 70 --'2013R2'
14        WHEN @dbversionno >= 71040 AND @dbversionno < 80190 THEN 80 --'2015'
15        WHEN @dbversionno >= 80190 AND @dbversionno < 91470 THEN 90 --'2016'
16        WHEN @dbversionno >= 91470 AND @dbversionno < 100550 THEN 100 --'2017'
17        WHEN @dbversionno >= 100550 THEN 110 --'2018'
18        --ELSE 0
19    END
20IF @NavVersion IS NULL BEGIN
21    RAISERROR('No NAV version found for databaseversion %i', 10, 1, @dbversionno)
22    SET noexec ON;
23END;
24PRINT ('NAV Version is ' + CAST(@NavVersion AS VARCHAR)) + ' (' + CAST(@dbversionno AS VARCHAR) + ')' ;
25 
26-- Get security identifier (SID) for specified user.  Login must be setup in SQL Server first.
27DECLARE @BinarySID BINARY(100);
28SELECT @BinarySID = sid FROM sys.syslogins WHERE name = @UserID;
29 
30-- Get (SID) for AD user. SUSER_SID works with SQl2015->
31IF @BinarySID IS NULL BEGIN
32  SET @BinarySID = SUSER_SID(@UserID)
33END;
34 
35IF @BinarySID IS NULL BEGIN
36    RAISERROR('SQL Server login not found for User %s.', 10, 1, @UserID)
37    SET noexec ON;
38END;
39 
40-- SID is stored in the User table as a formatted string.  Need to convert it.
41DECLARE @StringSID VARCHAR(238)
42DECLARE @i AS INT
43DECLARE @j AS INT
44DECLARE @Grp AS INT
45 
46SELECT @StringSID = 'S-'
47    + CONVERT(VARCHAR, CONVERT(INT, CONVERT(VARBINARY, SUBSTRING(@BinarySID, 1, 1))))
48SELECT @StringSID = @StringSID + '-'
49    + CONVERT(VARCHAR, CONVERT(INT, CONVERT(VARBINARY, SUBSTRING(@BinarySID, 3, 6))))
50 
51SET @j = 9
52SET @i = LEN(@BinarySID)
53SET @Grp = 1
54 
55WHILE (@j < @i) AND (@Grp <= 5) BEGIN
56  SET @Grp = @Grp + 1
57 
58  DECLARE @val BINARY(4)
59  SELECT @val = SUBSTRING(@BinarySID, @j, 4)
60 
61  SELECT @StringSID = @StringSID + '-'
62    + CONVERT(VARCHAR, CONVERT(BIGINT, CONVERT(VARBINARY, REVERSE(CONVERT(VARBINARY, @val)))))
63  SET @j = @j + 4
64END
65 
66-- Check to see if User record already exists
67DECLARE @UserGUID uniqueidentifier
68DECLARE @strInsert1 nvarchar(MAX);
69DECLARE @strInsert2 nvarchar(MAX);
70DECLARE @ParmDefinition nvarchar(MAX);
71 
72SELECT @UserGUID = [USER Security ID]
73    FROM [USER] WHERE [Windows Security ID] = @StringSID
74 
75IF @UserGUID IS NOT NULL
76    PRINT 'User ID ' + @UserID + ' already exists in User table.'
77 
78ELSE BEGIN
79    -- Generate new GUID for NAV security ID
80    SET @UserGUID = NEWID()
81 
82    -- Create User record
83    SET @strInsert1 = 'INSERT INTO [User] ([User Security ID], [User Name], [Full Name], [State], [Expiry Date], [Windows Security ID], [Change Password], [License Type]'
84    SET @strInsert2 = 'VALUES(@UserGUID1, @UserID1, '''', 0, ''1/1/1753'', @StringSID1, 0, 0'
85    SET @ParmDefinition = '@UserGUID1 uniqueidentifier, @UserID1 varchar(100), @StringSID1 varchar(238)'
86 
87    IF @NavVersion >= 70 BEGIN --add 2013R2
88        SET @strInsert1 = @strinsert1 + ', [Authentication Email]'
89        SET @strInsert2 = @strinsert2 + ', '''''
90    END;
91 
92    IF @NavVersion >= 90 BEGIN --add 2016
93        SET @strInsert1 = @strinsert1 + ', [Contact Email]'
94        SET @strInsert2 = @strinsert2 + ', '''''
95    END;
96 
97    IF @NavVersion >= 100 BEGIN --add 2017
98        SET @strInsert1 = @strinsert1 + ', [Application ID], [Exchange Identifier]'
99        SET @strInsert2 = @strinsert2 + ', ''00000000-0000-0000-0000-000000000000'', '''''
100    END;
101 
102    IF @NavVersion >= 110 BEGIN --add 2018
103        SET @strInsert1 = @strinsert1 + ', [Directory Role ID]'
104        SET @strInsert2 = @strinsert2 + ', '''''
105    END;
106 
107    SET @strinsert1 =  @strinsert1 +') ' + @strinsert2 + ') '
108    EXECUTE sp_executesql @strinsert1, @ParmDefinition, @UserGUID1=@UserGUID, @UserID1=@UserID, @StringSID1=@StringSID;
109 
110    PRINT 'Created User record for User ID ' + @UserID + '. - ' + CAST(@@ROWCOUNT AS VARCHAR) + ' row(s) affected.'
111END
112 
113-- Check to see if user is assigned to SUPER role
114IF EXISTS(SELECT * FROM [Access Control] WHERE [USER Security ID] = @UserGUID AND [ROLE ID] = 'SUPER' AND [Company Name] = '')
115    PRINT 'User ID ' + @UserID + ' is already assigned to SUPER role.'
116 
117ELSE BEGIN
118    -- Create Access Control record to add user to SUPER role
119    SET @strInsert1 = 'INSERT INTO [Access Control] ([User Security ID], [Role ID], [Company Name]'
120    SET @strInsert2 = 'VALUES(@UserGUID1, ''SUPER'', '''''
121    SET @ParmDefinition = '@UserGUID1 uniqueidentifier'
122 
123    IF @NavVersion >= 90 BEGIN --add 2016
124        SET @strInsert1 = @strinsert1 + ', [App ID], [Scope]'
125        SET @strInsert2 = @strinsert2 + ', ''00000000-0000-0000-0000-000000000000'',''0'''
126    END;
127 
128    SET @strinsert1 =  @strinsert1 +') ' + @strinsert2 + ') '
129    EXECUTE sp_executesql @strinsert1, @ParmDefinition, @UserGUID1=@UserGUID;
130    PRINT 'Added User ID ' + @UserID + ' to SUPER role. - ' + CAST(@@ROWCOUNT AS VARCHAR) + ' row(s) affected.'
131END
132 
133-- User Property record required to allow login
134IF EXISTS(SELECT * FROM [USER Property] WHERE [USER Security ID] = @UserGUID)
135    PRINT 'User ID ' + @UserID + ' already has User Property record.'
136 
137ELSE BEGIN
138    SET @strInsert1 = 'INSERT INTO [User Property] ([User Security ID], [Password], [Name Identifier], [Authentication Key], [WebServices Key], [WebServices Key Expiry Date]'
139    SET @strInsert2 = 'VALUES(@UserGUID1, '''', '''', '''', '''', ''1/1/1753'''
140    SET @ParmDefinition = '@UserGUID1 uniqueidentifier'
141 
142    IF @NavVersion >= 70 BEGIN --add 2013R2
143        SET @strInsert1 = @strinsert1 + ', [Authentication Object ID]'
144        SET @strInsert2 = @strinsert2 + ', '''''
145    END;
146 
147    IF @NavVersion >= 110 BEGIN --add 2018
148        SET @strInsert1 = @strinsert1 + ', [Directory Role ID]'
149        SET @strInsert2 = @strinsert2 + ', '''''
150    END;
151 
152    SET @strinsert1 =  @strinsert1 +') ' + @strinsert2 + ') '
153    EXECUTE sp_executesql @strinsert1, @ParmDefinition, @UserGUID1=@UserGUID;
154    PRINT 'Created User Property record for User ID ' + @UserID + '. - ' +  CAST(@@ROWCOUNT AS VARCHAR) + ' row(s) affected.'
155END;
156 
157SET NOEXEC OFF
158SET NOCOUNT OFF
159GO