NAV 4 login after SQL restore?

Rodtmclean

Hi,

I'm upgrading an old NAV 4 SP1 database to 2016. Someone has kindly restored a SQL database for me but no one knows any of the old database logins and I'm unable to login using windows logins . I can get into a NAV 2009+ database in these circumstances by removing records from various user tables but I'm not having much success here. Do I need to restore from .FBK and create a super user?

Thanks
Roddy

jglathe

Hi Roddy,

restore from .fbk would be easier. What is the technical version of the database? If it's 2009R2 you should be able to add a user from SQL.

with best regards

Jens

Rodtmclean

Hi Jens,

Its on 4 SP1, I'm trying to get it to 4 SP3 then onto NAV 2009. I need to open it in 4 SP3 first to disable a few things using alter database (maintain views, maintain relationships & maintain defaults). SQL restore was onto Windows 2008/SQL 2008 R2 which NAV 4 SP 1 doesn't talk to.

I think I'll start again

Thanks
Roddy

vaprog

What security model does your database have? Are you able to access it's data on SQL Server directly?

If so, you should be able to gain access from within NAV by purging all records from tables User, "Member Of", "Windows Login", and "Windows Access Control" on SQL, or, alternatively, add a row to "Windows Login" representing your windows user, and one to "Windows Access Control" giving that user the SUPER role.

I don't know how to reset permissions if your database uses the Enhanced security model.

mdPartnerNL

-- NAV TECHDAYS 2014 - Alain Krikilion

-- Clean the NAV security tables


--Clean the login tables

--PRE-NAV2013
DELETE FROM dbo.[Member Of];
DELETE FROM dbo.[User];
DELETE FROM dbo.[Windows Access Control];
DELETE FROM dbo.[Windows Login];

--NAV2013+
DELETE FROM [dbo].[Access Control]
DELETE FROM [dbo].[User]
DELETE FROM [dbo].[User Property]

DELETE FROM dbo.[User Personalization]
DELETE FROM dbo.[User Metadata]
DELETE FROM dbo.[Page Data Personalization]

Rodtmclean

Hi,

I created a new blank database on the same upgrade srever, created a windows login and then copied and pasted the SID from Windows Login to the Windows Login table in the upgrade database (using SQL Management studio) and then set up a Windows Access Control Record by hand again through SQL. I was just thinking about doing this when the database login created via SQL had not worked earlier.

Regards
Roddy

vaprog

You can get the SID from windows in other ways e.g. get your own SID by executing

whoami /user

on a command line, or a list of users and SID by

wmic useraccount GET Caption,SID

r3ign

We use this to get SID then insert at SQL. it's much easier for us devs.


======================================
PowerShell ISE script
=======================================
$objUser = New-Object System.Security.Principal.NTAccount("retail-associates.com\gil")
$strSID = $objUser.translate([System.Security.Principal.SecurityIdentifier])
$strSID.Value







SQL Script
==================================

use [Test]

declare @USERSID uniqueidentifier, @WINDOWSSID nvarchar(119), @USERNAME nvarchar(50), @USERIDTXT varchar(50)

Select NEWID()
set @USERNAME = 'retail-associates\gil'
set @USERSID = NEWID()
set @USERIDTXT = CONVERT(varchar(50), @USERSID)
set @WINDOWSSID = 'S-1-5-21-1108989220-2322889631-4127943521-1261'

insert into [dbo].[User] ([User Security ID],[User Name],[Full Name],[State],[Expiry Date],[Windows Security ID],
[Change Password],[License Type],[Authentication Email])
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],[Authentication Object ID])
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