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

Comment List
  • Hi Waldo,

    Encountered the same issue, but I made a T-SQL that simply adds the required user, leaving the customers in.

    This is the script:

    USE [Demo Database NAV (7-0)]

    DECLARE @USERSID uniqueidentifier, @WINDOWSSID nvarchar(119), @USERNAME nvarchar(50)

    SET @USERNAME   = '<domain\user>'

    SET @USERSID    = '<random security id>'

    SET @WINDOWSSID = '<windows security id>'

    INSERT INTO [dbo].[User]

              ([User Security ID],[User Name],[Full Name],[State],[Expiry Date],

               [Windows Security ID],[Change Password],[License Type])

        VALUES

              (@USERSID,@USERNAME,'',0,'1753-01-01 00:00:00.000',@WINDOWSSID,0,0)

    INSERT INTO [dbo].[User Property]

              ([User Security ID],[Password],[Name Identifier],[Authentication Key],[WebServices Key],[WebServices Key Expiry Date])

        VALUES

              (@USERSID,'','','','','1753-01-01 00:00:00.000')

    INSERT INTO [dbo].[Access Control]

              ([User Security ID],[Role ID],[Company Name])

        VALUES

              (@USERSID,'SUPER','')

    GO

Related
Recommended