Use the following SQL query to add a Windows user with the permission set for Microsoft Dynamics Navision 2013 R2 or 2015. Before executing the query in the relevant SQL database, the windows user of the particular windows account, the database name of the particular database, and permission that 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
GOCode language: HTML, XML (xml)Visit the next tutorial to learn about, how to add Windows users to Dynamics NAV using Windows PowerShell.
Vidura Dananjaya
Senior Solutions Architect - Microsoft Dynamics Navision / Microsoft Dynamics 365 Business Central and freelance developer. (The admin of NAVUSER)