If you have been working with NAV for quite some time, you've definitely been in this situation: you have just received a NAV DB backup from a customer or colleague, restored on your local machine, tried to login, and... oops, the user does not exist or is invalid. Well, actually native NAV backups would let you add a user before you closed the client after backup restore (how many of you closed it and had to restart the process all over again? :)).
When it came to SQL backup, the story was a bit different. You either had to get the login from the person that gave you the backup, or, force your way in by adding login credentials to "Windows Logon" and "Windows Account Control" tables directly, using SQL Server Management studio.
Armed with this knowledge, I was restoring NAV 2013 backup. But when it came to the usual routine of adding the login credentials to the DB, I was greeted by two surprises:
Well, isn't that a nice challenge for a Friday afternoon? So, I went ahead and figured this one out. If you ever need it, here it goes.
Before we start...
Disclaimer: this is not an official solution sugested by Microsoft or NAV engineers. This is just a knowledge sharing tip, and the author will not bear any responsibility if by following it you wreck your database, NAV installation or anything that was in any way related to this procedure.
If you're still here, here's what you will need:
We are making an assumption that the database to which you need to add is already present on the SQL server.
First, you need to have or obtain the SID for your user account. If you do not have it, here's a simple way to obtain it
Open command prompt - click Start, Run..., then type
wmic useraccount get name, sid
|timestamp||User Security ID||User Name||Full Name||State||Expiry Date||Windows Security ID||Change Password||Licence Type||Authentication Email|
Notes on some of the field values:
a) "timestamp": leave this as NULL. SQL assigns this automatically.
b) "User Security ID": any GUID value will do here, as long as it's unique. Make a note of its this value - you'll need it for a later step.
c) "User Name": well, this is the windows user account name, of course. The one you need added.
d) "Expiry Date": "1753-01-01". In case you didn't know, that's how NAV 0D (zero date) is stored in SQL
e) "Windows Security ID": this is the value we found earlier with the command prompt, for the account you want to add.
f) For the rest of the fields, be sure to enter 0 or a blank value - don't leave them as NULLs.
Next, we need to add an entry to the "User Property" table. This is the new bit that is required since NAV 2013. Locate it with SSMS like the previous one, and add a new record:
"User Security ID" should be the same as in the last step. Don't forget to put in the blanks in the remaining fields.
|timestamp||User Security ID||Role ID||Company Name|
Notes on the field values:
a) "User Security ID" - again, same value as before. Don't confuse this with "Windows Security ID" - that's another thing altogether!
b) "Role ID" - NAV security rele code. You probably want "SUPER" for this, if you are using standard NAV security setup.
c) "Company Name": blank, meaning the access is granted to all companies.
That's it, you should be good to go!
Once again, this is probably not the most proper way to do this, but it works.