Skip to main content

Insert SUPER User

Copy the command into Microsoft SQL Server Management Studio.

info

Before running the command you need to change

  • {DATABASE_NAME}
  • {DOMAIN\UserID}
  • {USERSID}
note

The USERSID can be found through this guide Get User SID

SET NOCOUNT ON
GO

USE [{DATABASE_NAME}];
GO

DECLARE @UserID varchar(100)
SET @UserID = '{DOMAIN\UserID}'

-- 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], [Authentication Email], [Application ID], [Contact Email], [Exchange Identifier])
VALUES(@UserGUID, @UserID, '', 0, '1/1/1753', @StringSID, 0, 0, '', '00000000-0000-0000-0000-000000000000', '', '')  

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], [App ID], [Scope])
VALUES(@UserGUID, 'SUPER', '', '00000000-0000-0000-0000-000000000000', 0) 

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], [Authentication Object ID], [Directory Role ID])
VALUES(@UserGUID, '', '', '', '', '1/1/1753', '', '00000000-0000-0000-0000-000000000000')

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

SET NOCOUNT OFF
GO