Add Users in NAV 2016 from SQL
Server
Hi
all,
Today i came across with different type of requirement, Sometimes you might be got an error while opening the RTC. Error – ”You do not have permission to access .....”. See the bellow screenshot.
This
error came, because user not exist in User Table, So if you are a first user
then simply you can execute the SQL query, and you will resolve your issue.
The
Sql Query is given bellow, just Open your Database and run this query.
USE [Demo Database NAV (9-1)]
GO
DELETE FROM
[dbo].[User]
DELETE FROM
[dbo].[Access Control]
DELETE FROM
[dbo].[User Property]
DELETE FROM
[dbo].[Page Data Personalization]
DELETE FROM
[dbo].[User Default Style Sheet]
DELETE FROM
[dbo].[User Metadata]
DELETE FROM
[dbo].[User Personalization]
GO
Now you are abale to open RTC.
Note: But
whenever you have multiple users then you can’t execute this query because all users will be deleted, for that
you have to do some more steps, that steps i’m going to describe.
Step 1: Open Powershell ISE with Administrator
privilege. Now execute this command
$objUser =
New-Object System.Security.Principal.NTAccount("DOMAIN\BINESH.SINGH")
$strSID =
$objUser.Translate([System.Security.Principal.SecurityIdentifier])
$strSID.Value
See the bellow
screenshot for better understanding.
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.
Step
2: Now open SQL Server and you need to
execute the following Query for that Database in which you wants your
respective login. SID and the Username copied from above.
USE [Demo
Database NAV (9-1)]
GO
DECLARE @USERSID uniqueidentifier,
@WINDOWSSID nvarchar(119), @USERNAME nvarchar(50), @USERSIDTXT varchar(50)
SET @USERNAME
= 'DOMAIN\BINESH.SINGH'
SET @USERSID = NEWID()
SET
@USERSIDTXT = CONVERT(VARCHAR(50), @USERSID)
SET
@WINDOWSSID = 'S-1-5-21-611616715-3189593580-2499604960-1499'
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])
VALUES
(@USERSID,@USERNAME,'Binesh Singh',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],[Scope],[App
ID])
VALUES
(@USERSID,'SUPER','',0,'{00000000-0000-0000-0000-000000000000}')
GO
See the bellow screenshot
for Sql Query.
Execute the above
command. If it gets successfully executed then you can go and Restart the NAV
Server Instances and then try to login and you will get logged in to RTC.
