Create a super user on a restored Dynamics NAV 2013/2013R2 Database in SQL Script

shutterstock_269568563

 

Ever since Microsoft released Dynamics NAV 2013, there has been a discussion on the best way to create a user in a restored database.

 

The problem is this:

 

When a SQL backup is made and ported to a different environment, the Windows or Active Directory users that were created in the old environment are not necessarily created in the new environment.

 

It is always possible to restore the backup on the new SQL server, but that does not give me access to the database.

 

The easy way is to delete all information in four tables, but that will delete all existing users. If they are not needed then it is no problem, but if we want to keep the existing users then, this is not an option.

 

Deleting all users can be done running the following script.

clip_image002

 

If the user names are precisely the same just with a different domain then it is possible to “update” the windows security id with this script.

clip_image004

 

Combined with the syslogin table and a little SQL magic, it may even be possible to change all users from one domain to another and from one Windows Security id to another.

But what if I want a script to run, that will:

· Create a user on the SQL server

· Generate a random User SID

· Create the user in the database

· Add a role to the user

· Give permission to the user in the new database

 

In one go.

 

It took a bit of Googling and help from dailycoding.com, Daniel Rimmelzwaan on Mibuso and other resources to compile this script, which I pass on to you without any guaranties and to serve as inspiration. Just add information on the username, the database and the permissions you want to assign to the user and run the script.

 

And you are in.

 

The scripts for 2013 and 2013R2 can be downloaded from here.

clip_image006

clip_image008

clip_image009

clip_image011

clip_image013

 

 

 

image

Anonymous
Related