NAV 4 login after SQL restore?

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

Comments

  • jglathejglathe Member Posts: 639
    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
  • RodtmcleanRodtmclean Member Posts: 83
    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
  • vaprogvaprog Member Posts: 1,116
    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.
  • mdPartnerNLmdPartnerNL Member Posts: 802
    -- 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]

  • RodtmcleanRodtmclean Member Posts: 83
    edited 2016-02-05
    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
  • vaprogvaprog Member Posts: 1,116
    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
    
  • r3ignr3ign Member Posts: 10
    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
Sign In or Register to comment.