Hi Everyone,
In this article, I will explain how you can create new user in Dynamics 365 Business Central on premise when user not able to access NAV.
For this example I will use 2 scripts first script will be powershell script that will used to get the WINDOWSSID, this SSID will be used in second script that we have to execute on the db.
PowerShell Script: Step - 1
$objUser = New-Object System.Security.Principal.NTAccount("MATH","farez")
$strSID = $objUser.Translate([System.Security.Principal.SecurityIdentifier])
$strSID.Value
S-1-5-21-1036304282-4254897155-4282514468-1001
Use the resulted SSID in SQL Query Script.
SQL Query Script: Step - 2
--------------------------------------------------------
DECLARE @USERSID uniqueidentifier, @WINDOWSSID nvarchar(119), @USERNAME nvarchar(50), @USERSIDTXT varchar(50)
SET @USERNAME = 'MATH\UserName'
SET @USERSID = NEWID()
SET @USERSIDTXT = CONVERT(VARCHAR(50), @USERSID)
SET @WINDOWSSID = 'S-1-5-21-3497914956-3715460544-469535843-1010'
INSERT INTO [dbo].[User]
([User Security ID],[User Name],[Full Name],[State],[Expiry Date],[Windows Security ID],[Change Password],[License Type]
,[Authentication Email],[Contact Email],[Exchange Identifier],[Application ID])
VALUES
(@USERSID,@USERNAME,'',0,'1753-01-01 00:00:00.000',@WINDOWSSID,0,0,'','tabrezajaz@gmail.com','','00000000-0000-0000-0000-000000000000')
INSERT INTO [dbo].[User Property]
([User Security ID],[Password],[Name Identifier],[Authentication Key],[WebServices Key],[WebServices Key Expiry Date],
[Authentication Object ID],[Directory Role ID])
VALUES
(@USERSID,'','','','','1753-01-01 00:00:00.000','','')
INSERT INTO [dbo].[Access Control]
([User Security ID],[Role ID],[Company Name],[Scope],[App ID])
VALUES
(@USERSID,'SUPER','',0,'00000000-0000-0000-0000-000000000000')
GO
--------------------------------------------------------
Above script create different entries in User, User Property, and Access Control tables.
Other important queries:
select * from [dbo].[User]
select * from [dbo].[User Property]
select * from [dbo].[Access Control]
Delete Query:
delete from [dbo].[User] where [User Security ID] ='6A294094-AF01-423B-A4FD-79A40F9B6392'
delete from [dbo].[Access Control] where [User Security ID] ='6A294094-AF01-423B-A4FD-79A40F9B6392'
Wallah!
I hope you understand how to create users for Dynamics 365 Business Central manually using SQL and PowerShell script.
Stay Tuned!
![]()
