SQL Script to create login in NAV 2013

Brett_Barabash

We've all been there at one time or another. After restoring a customer database to your server, you find yourself unable to login with your user account.

In previous versions of NAV, you could simply insert a record containing a SQL Server user id into the User and Member Of tables. Not quite as simple in NAV 2013, since it uses Windows Authentication exclusively.

I created the following SQL Server script to add a specified Windows login to the NAV 2013 database and assign it to the SUPER role. The login must exist in SQL Server before running this script.

SET NOCOUNT ON
GO

USE [Database Name]  -- NAV 2013 database you want to use
GO

DECLARE @UserID varchar(100)
SET @UserID = 'DOMAIN\username'  -- Windows Login you want to add

-- Get security identifier (SID) for specified user.  Login must be setup in SQL Server first.
DECLARE @BinarySID binary(100)
SELECT @BinarySID = sid FROM sys.syslogins WHERE name = @UserID

IF @BinarySID IS NULL
  RAISERROR('SQL Server login not found for User %s.', 10, 1, @UserID)

-- SID is stored in the User table as a formatted string.  Need to convert it.
DECLARE @StringSID varchar(238)
DECLARE @i AS int
DECLARE @j AS int
DECLARE @Grp AS int

SELECT @StringSID = 'S-'
    + CONVERT(VARCHAR, CONVERT(INT, CONVERT(VARBINARY, SUBSTRING(@BinarySID, 1, 1)))) 
SELECT @StringSID = @StringSID + '-'
    + CONVERT(VARCHAR, CONVERT(INT, CONVERT(VARBINARY, SUBSTRING(@BinarySID, 3, 6))))

SET @j = 9
SET @i = LEN(@BinarySID)
SET @Grp = 1

WHILE (@j < @i) AND (@Grp <= 5) BEGIN
  SET @Grp = @Grp + 1

  DECLARE @val BINARY(4)
  SELECT @val = SUBSTRING(@BinarySID, @j, 4)

  SELECT @StringSID = @StringSID + '-'
    + CONVERT(VARCHAR, CONVERT(BIGINT, CONVERT(VARBINARY, REVERSE(CONVERT(VARBINARY, @val))))) 
  SET @j = @j + 4  
END

-- Check to see if User record already exists
DECLARE @UserGUID uniqueidentifier

SELECT @UserGUID = [User Security ID] 
FROM [User] WHERE [Windows Security ID] = @StringSID

IF @UserGUID IS NOT NULL
  PRINT 'User ID ' + @UserID + ' already exists in User table.'
  
ELSE BEGIN
  -- Generate new GUID for NAV security ID
  SET @UserGUID = NEWID()
  
  -- Create User record
  INSERT INTO [User]
  ([User Security ID], [User Name], [Full Name], [State], [Expiry Date], [Windows Security ID], [Change Password],	[License Type])
  VALUES(@UserGUID, @UserID, '', 0, '1/1/1753', @StringSID, 0, 0)  

  PRINT 'Created User record for User ID ' + @UserID + '. - ' + CAST(@@ROWCOUNT AS varchar) + ' row(s) affected.'
END

-- Check to see if user is assigned to SUPER role  
IF EXISTS(SELECT * FROM [Access Control] WHERE [User Security ID] = @UserGUID AND [Role ID] = 'SUPER' AND [Company Name] = '')
  PRINT 'User ID ' + @UserID + ' is already assigned to SUPER role.'
  
ELSE BEGIN  
  -- Create Access Control record to add user to SUPER role
  INSERT INTO [Access Control]
  ([User Security ID], [Role ID], [Company Name])
  VALUES(@UserGUID, 'SUPER', '') 

  PRINT 'Added User ID ' + @UserID + ' to SUPER role. - ' + CAST(@@ROWCOUNT AS varchar) + ' row(s) affected.'
END 

-- User Property record required to allow login
IF EXISTS(SELECT * FROM [User Property] WHERE [User Security ID] = @UserGUID)
  PRINT 'User Property record already exists for User ID ' + @UserID + '.'
  
ELSE BEGIN
  INSERT INTO [User Property]
  ([User Security ID], [Password], [Name Identifier], [Authentication Key], [WebServices Key], [WebServices Key Expiry Date])
  VALUES(@UserGUID, '', '', '', '', '1/1/1753')

  PRINT 'Created User Property record for User ID ' + @UserID + '. - ' +  CAST(@@ROWCOUNT AS varchar) + ' row(s) affected.'
END

SET NOCOUNT OFF
GO