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 :-).