Cover Image

Add windows user using SQL query !

This following query is to add windows user for Navision 2013 R2 or 2015.
Before executing this query in the relevant sql database, windows user of the particular windows account, database name of the particular database and permission which is relevant to the permission set should be changed.


USE [master]
/* Declare variables */
DECLARE @USERSID uniqueidentifier, @USERSIDTXT varchar(50), @PERMISSION varchar(50), @USERNAME varchar(50), @WINDOWSSID varchar(119), @DATABASENAME varchar(50), @BinSid VARBINARY(100), @t nvarchar(4000)
/* Username, database and permissions must be entered here */
SET @USERNAME = 'Windows Account (Domain User)'
SET @DATABASENAME = 'Database Name'
SET @PERMISSION = 'Permission Set'

/* Generate a random unique id */
SELECT NEWID()
SET @USERSID = NEWID()
SET @USERSIDTXT = CONVERT(VARCHAR(50), @USERSID)

/* Create Loin if necessary */
BEGIN TRY
SET @t = N'CREATE LOGIN [' + @USERNAME + '] FROM WINDOWS'
exec sys.sp_executesql @t END TRY
BEGIN CATCH
END CATCH

/* Get the WindowsSID from the SQL server */
SET @BinSid = (SELECT sid FROM syslogins where loginname=@USERNAME)

/* Convert the uniqueidentifier to NAV SID */
IF LEN(@BinSID) % 4 <> 0 raiserror('User Name not found', 20, -1) with log

DECLARE @i AS INT
DECLARE @j AS INT

SELECT @WINDOWSSID = 'S-'
+ CONVERT(VARCHAR, CONVERT(INT, CONVERT(VARBINARY, SUBSTRING(@BinSID, 1, 1))))
SELECT @WINDOWSSID = @WINDOWSSID + '-'
+ CONVERT(VARCHAR, CONVERT(INT, CONVERT(VARBINARY, SUBSTRING(@BinSID, 3, 6))))

SET @j = 9
SET @i = LEN(@BinSID)

WHILE @j < @i
BEGIN
DECLARE @val BINARY(4)
SELECT @val = SUBSTRING(@BinSID, @j, 4)
SELECT @WINDOWSSID = @WINDOWSSID + '-'
+ CONVERT(VARCHAR, CONVERT(BIGINT, CONVERT(VARBINARY, REVERSE(CONVERT(VARBINARY, @val)))))
SET @j = @j + 4
END
/* Change database */
SET @t = N'USE [' + @DATABASENAME +']'
exec sys.sp_executesql @t

/* Create user */
SET @t = N'CREATE USER [' + @USERNAME + '] FOR LOGIN [' + @USERNAME + ']'
exec sys.sp_executesql @t

/* Add role to user */
SET @t = N'sp_addrolemember ' + '''' + 'db_owner' + ''''+ ', ' + '''' + @USERNAME + ''''
exec sys.sp_executesql @t

/* Insert user in database */
SET @t = N'INSERT INTO [' + @DATABASENAME +'].[dbo].[User] '+
'([User Security ID]
,[User Name]
,[Full Name]
,[State]
,[Change Password]
,[Expiry Date]
,[Windows Security ID]
,[License Type]
,[Authentication Email])

VALUES
(' + '''' + @USERSIDTXT + '''' +
',' + '''' + @USERNAME + '''' +
',' + '''' + '''' +
',' + '0' +
',' + '0' +
',' + '''' + '1753-01-01 00:00:00.000' + '''' +
','+ ''''+ @WINDOWSSID + '''' +
',' + '0' +
',' + '''' + '''' +')'
exec sys.sp_executesql @t

/* Add permissions */
SET @t = 'INSERT INTO [' + @DATABASENAME +'].[dbo].[Access Control]
([User Security ID]
,[Role ID]
,[Company Name])
VALUES
(' + '''' + @USERSIDTXT + '''' +
',' + '''' + @PERMISSION + '''' +
',' + '''' + '''' + ')'
exec sys.sp_executesql @t

/* Add User Property */
SET @t = 'INSERT INTO [' + @DATABASENAME +'].[dbo].[User Property]
([User Security ID]
,[Password]
,[Name Identifier]
,[Authentication Key]
,[WebServices Key]
,[WebServices Key Expiry Date]
,[Authentication Object ID])
VALUES
(' + '''' + @USERSIDTXT + '''' +
',' + '''' + '''' +
',' + '''' + '''' +
',' + '''' + '''' +
',' + '''' + '''' +
',' + '''' + '1753-01-01 00:00:00.000' + '''' +
',' + '''' + '''' +')'

exec sys.sp_executesql @t
GO

Development Special