Add Users in NAV 2016 from SQL Server

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.

POWERSHELL SCRIPT CANNOT BE LOADED BECAUSE RUNNING SCRIPTS IS DISABLED

WINDOWS 8 – POWERSHELL SCRIPT CANNOT BE LOADED BECAUSE RUNNING SCRIPTS IS DISABLED

I just stumbled upon an interesting Technet blog for Windows scripts here.

Attempting to run a Powershell script however generated the following error: [See bellow screenshot].

 The link provides plenty of information – so this is a precis of what you need to know, as well as the change to make to run Powershell scripts.

First let’s take a look at the Powershell execution policies – Restricted is the default policy and it prevents the running of scripts.

Our other alternatives are:

AllSigned – Scripts can run but must be signed by a trusted publisher

RemoteSigned – Scripts can run. Trusted publisher scripts must be signed. Scripts that you have written yourself do not need to be digitally signed.

Unrestricted – Unsigned scripts can be run and the user is warned before running downloaded scripts.

Bypass – nothing is blocked and there are no warnings or prompts.

For full disclosure on these policies review the Execution Policies link above.

Choose your alternative Execution Policy carefully – Restricted is the only policy that can save you from running malicious code (signed or unsigned) on your system!

Next we need to be aware of the Execution Policy scopes:

Process – only affects the current Powershell process.

CurrentUser – affects only the current user.

LocalMachine – affects all users on the computer.

So now let’s open up Powershell and review our current Execution Policy. From the Start screen just type Powershell and click on it in the search results.

Enter the following command to review the current policy:

Get-ExecutionPolicy -List

As you can see my default policy is Undefined (which is the same thing as Restricted):

So now we can change the Execution Policy for the CurrentUser Scope to RemoteSigned. If you wish to choose a different scope and/or policy then be my guest:

Set-ExecutionPolicy RemoteSigned -Scope CurrentUser

Note: You will see the following warning – press Enter to continue: [See the bellow screenshot].

              We can now check and see that the policy has been changed:

Get-ExecutionPolicy -List

To finally run the Powershell script I had to run Powershell as an Administrator (right click and Run As Administrator).

Now enjoy your Powershell !

-------------------------- EXAMPLE 1 --------------------------

Thanks & Best Wishes

Binesh Singh Rajput

(MCP, MS, MCTS)