Make NAV User – SQL + PowerShell

Hey Friends,

It’s a very simple Post on how to make a NAV User from SQL/Powershell. Many times we end up with the following Error.

0

Though if you are first user login in then you can follow this POST to quickly solve it out, but if there are many valid users then obviously not.

So the first step that need to be done is to open Powershell ISE with Administrator privilege. Now execute this command

capture

//RWN
$objUser = New-Object System.Security.Principal.NTAccount(“DOMAIN\USER”)
$strSID = $objUser.Translate([System.Security.Principal.SecurityIdentifier])
$strSID.Value
//RWN

Once you will execute the above command, you will get the SID number. Save it off in Notepad OR somewhere. It will be used in the second step.

Now open SQL Server and you need to execute the following Query for that Database in which you wants your respective login. Paste the SID and the Username copied above. 

capture

–RWN
DECLARE @USERSID uniqueidentifier, @WINDOWSSID nvarchar(119), @USERNAME nvarchar(50), @USERSIDTXT varchar(50)

SET @USERNAME = ‘DOMAIN\USER
SET @USERSID = NEWID()
SET @USERSIDTXT = CONVERT(VARCHAR(50), @USERSID)
SET @WINDOWSSID = ‘YOUR COPIED SID

INSERT INTO [dbo].[User]
([User Security ID],[User Name],[Full Name],[State],[Expiry Date],[Windows Security ID],[Change Password],[License Type]
,[Authentication Email])
VALUES
(@USERSID,@USERNAME,”,0,’1753-01-01 00:00:00.000′,@WINDOWSSID,0,0,”)

INSERT INTO [dbo].[User Property]
([User Security ID],[Password],[Name Identifier],[Authentication Key],[WebServices Key],[WebServices Key Expiry Date],
[Authentication Object ID])
VALUES
(@USERSID,”,”,”,”,’1753-01-01 00:00:00.000′,”)

INSERT INTO [dbo].[Access Control]
([User Security ID],[Role ID],[Company Name])
VALUES
(@USERSID,’SUPER’,”)
GO
–RWN

Execute the above command. If it gets successfully executed then you can go and try to login and you will get logged in to RTC.

Advertisements

2 comments on “Make NAV User – SQL + PowerShell

  1. Pingback: Make NAV User – SQL + PowerShell - Microsoft Dynamics NAV Community

  2. Pingback: Make NAV User – SQL + PowerShell - Dynamics NAV by RockWithNAV - Dynamics NAV Users - DUG

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s