You are here

Adding User Account in NAV 2013 via SQL

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:

  1. First, "Windows Logon" and "Windows Account Control" tables did not exist anymore. After some digging around, I found two similar ones: "User" and "Access Control" and decided to use those.
  2. Still, that didn't quite work as expected - I was greeted by a completely new error:
    Error Message: You do not have access to Microsoft Dynamics NAV. Verify that you have been set up as a valid user in Microsoft Dynamics NAV.

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.

How to add a user account to NAV 2013 DB via SQL backend

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:

  • SQL Server Management Studio (or alternative SQL tool) and full access to your NAV 2013 DB on SQL server
  • The Windows security Identifier (SID) for the user account you want to add. There will be an explanation how to get it, if you do not have it.

We are making an assumption that the database to which you need to add is already present on the SQL server.

The steps 

  1. 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

    1. Open command prompt - click Start, Run..., then type

      cmd
      
    2. Within the command prompt window, type:
      wmic useraccount get name, sid
    3. This should give you the list of SIDs known to your machine, something like this screen:
    4. Make a note/copy the account SID that you need. In my case, it's the last item in the list.
       
  2. Now, let's edit the NAV "User" table via SQL.
    Start SQL Server Management Studio and locate the NAV DB that you need to access. Expand the database Tables branch and locate the "User" table. Right-click on it, and select "Edit Top 200 Rows":

     
  3. You should get a list of user records similar to this:
  4. Add another row with your account data. This should look something like this:
    timestamp User Security ID User Name Full Name State Expiry Date Windows Security ID Change Password Licence Type Authentication Email
    NULL 53749235-3faa-44d8-82fc-bebf837c66c9 NB0001\VJAKAS Vytenis Jakas 0 1753-01-01 S-1-5-21-291695581-3465666339-3187098927-1000 0 0  

    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.

  5. 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.

  6. And, the remaining entry, in the "Access Control" table:
    timestamp User Security ID Role ID Company Name
    NULL 53749235-3faa-44d8-82fc-bebf837c66c9 SUPER  

    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.

  7. That's it, you should be good to go!

Conclusion

Once again, this is probably not the most proper way to do this, but it works.

Comments

Can you provide a script for that?

Add new comment

LinkedIn Google+ Facebook YouTube Twitter

Theme by Danetsoft and Danang Probo Sayekti inspired by Maksimer