NAV 2013: How to log in any database

When you’re a partner .. you deal with databases of your customers, right? At least – you should ;-). Usually, there is “some” security set up at the customers site. At least let’s hope so ;-).

It might happen that you want to copy a customer’s database to your site, just to test, debug, do extra development, test why that MRP won’t run, do nasty things, .. you know .. do partner stuff.

What many of us do, is: take a backup of that database (or company) and restore it locally in the office. Problem is: there is security set up in that database, so there is no way to get in, even after a restore, we get this:


In NAV2009, we had the chance (at least in the native restore) to reset the security immediately after the restore, but in NAV2013, we first have to be able to log into the database in RTC to be able to set up security. So there’s no obvious “bridge” anymore that we can take.. .

How do we force ourselves into that DB?

Your first guess would be .. thinking of NAV2013 and all the new great features in it …

Powershell!

That would be a safe bet, wouldn’t it? In NAV2013, you have all these great new Cmdlets at your disposal …


And as you can see .. you can set up security. My guess would be: let’s add myself into the system.

So, first, I look up my sid in a normal command shell (not powershell) with

wmic useraccount get name,sid


I copy the Sid I want, go back to Powershell, and do this:

New-NAVServerUser TestFBKRestore -sid MyCopiedSid

The result is somewhat disappointing .. but understandable:


You obviously need access to be able to add users.. .

What about SQL Server?

We all know the trick in the old days to reset security straight on SQL Server – deleting the content of certain tables… Well .. may be it’s also possible in NAV2013.

I think it’s safe to assume that the person who is trying to restore that database, is sysadmin on the SQL Server where it’s restoring the database. Well, run this script on your new database:

delete
from
[dbo].[User]

delete
from
[dbo].[Access Control]

delete
from
[dbo].[User Property]

— And you’ll see you’ll be able to get back in :-). In fact .. It’s even better to clear a few more tables, just for deleting redundant data

delete
from
[dbo].[Page Data Personalization]

delete from
[dbo].[User Default Style Sheet]

delete from
[dbo].[User Metadata]

delete from
[dbo].[User Personalization]

Hope it’s useful for you.

And of course .. If you have comments or a better way .. I definitely want to know! Leave a comment :-).

5.00 avg. rating (98% score) - 3 votes

Permanent link to this article: https://www.waldo.be/2012/12/05/nav-2013-how-to-log-in-any-database/

3 comments

2 pings

    • Jens on March 30, 2016 at 1:55 pm
    • Reply

    Hi.
    Is it possible to use the New-NAVServerUser command on a NAV-Server which is not the NAV-Server where the instance is running where I want to add the user ?

    Example:
    I start the PowerShell console on Server A. There I want to call New-NAVServerUser for an instance which is running on Server B.

    Hope it gets clear what I mean 😉

    Thank you!

      • waldo on April 1, 2016 at 11:07 am
        Author

      As far as I know, you can do remote-powershell (set up a remote session, I mean), and that way, you would be able to execute Powershell from machine A, but actually working remotely on Machine B.
      Is this what you need to know?

      • Jens on April 4, 2016 at 7:46 am

      Yes, I will try it this way. Thank you!

  1. […] This is already described by other bloggers, such as Waldo in this post. […]

  2. […] This is already described by other bloggers, such as Waldo in this post. […]

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.