clausl: How to gain access to a database you do not have access to

[Blog bot]

I know this subject has been addressed before on other blogs, but I like to have things I can just copy and paste, so this blog post is mainly for my self and other people as lazy as me

I will address how to get access to both a NAV 2009 and a NAV 2013 database. Scenario is that you have received a SQL backup from a customer. You do not have access to the database and need to get access to the database from Role Tailored client(RTC).

Here is how you get access:

NAV 2013:

Before running below script you need two ID’s.

USERSID and WINDOWSSID

USERSID is new for NAV 2013. USERSID is generated when a user is created in RTC. But we do not have access to the database, so we need to create this ID manually and apply this to the database. In my example I have used  ‘586dd133-71e5-4cde-be08-625e7580e2a5′ as my USERID, but I could also have used  ‘586dd133-71e5-4cde-be08-538e7580e2a5′ or ‘586dd133-53e5-4cde-be08-538e7580e2a5′. If you follow this syntax you are good to go. And of course verify that the ID has not been used before in the 3 tables: User, User Property and Access Control.

WINDOWSSID we also had to use for NAV 2009 and you get by running this command in a Command Prompt, but you probably know this:

wmic useraccount get name,sid

If you are on a domain, this command will retrieve all ID’s from all your fellow employees on your domain, so this can take a while. So I usually just use this little GetSID tool to get my WINDOWSSID. You can download if from my SkyDrive here:

http://sdrv.ms/U7geW3

OK, so now you have both your USERSID and WINDOWSSID. It’s time to run the script, so you can gain access to the database from RTC.

All you need to do is to replace my details in BOLD with your details and run the script from inside ”SQL Server Management Studio”

USE [Demo Database NAV (7-0) Supply Chain Box]

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

SET @USERNAME   = ‘ABAKIONCLAUSL‘

SET @USERSID    = ‘586dd133-71e5-4cde-be08-625e7580e2a5‘

SET @WINDOWSSID = ‘S-2-5-21-3479854292-2160187097-728769791-8173‘

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

You know have access to the NAV 2013 database.

NAV 2009

For NAV 2009 is a bit more simple, here you just need to run following script from inside ”SQL Server Management Studio”. Again just replace my details mark with BOLD.

USE [Demo Database NAV (6-0) Supply Chain Box]

INSERT INTO [Windows Login] ([SID]) values (’S-2-5-21-3479854292-2160187097-728769791-8173‘);

INSERT INTO [Windows Access Control] ([Login SID],[Role ID],[Company Name])

values (’S-2-5-21-3479854292-2160187097-728769791-8173‘,’SUPER’,”);

GO

You know have access to the NAV 2009 database.

Happy copy and paste.

/Claus Lundstrøm



Читать дальше