Insert SUPER User

PowerShell

The script imports the Microsoft.Dynamics.Nav.Management.dll module, which provides the cmdlets that are used to manage users and tenants.

info

Before running the script please fill out the variables

• $ServerInstance is the name of the server instance on which the user should be created.
• $WindowsAccount is the windows account of the user that will be added.
• $Tenant is an optional parameter, if you want to create the user in a specific tenant, otherwise leave it blank.
• $ProfileId is the profile Id of the user. The script is using the SUPER profile id by default, you can change it according to your requirement.
• $NSTFolderPath is the path to the NAV Administration Shell Tools folder.

It's worth mentioning that the script should be run on the same machine as the Business Central Server Instance or the server instance should be reachable using the specified name.

The script checks whether the $Tenant variable is empty or not, if it's empty the script will use New-NAVServerUser cmdlet to create a new user with the WindowsAccount and ProfileId specified in the script and the -ServerInstance parameter, which specifies the server instance where the user will be created.

If $Tenant is not empty, the script will use New-NAVServerUser cmdlet with an additional -Tenant parameter which specifies the tenant in which the user will be created.

#-------------------------------PRE STEP------------------------------- 
$ServerInstance = ""
$WindowsAccount = ""
$Tenant = ""
$ProfileId = "SUPER"
$NSTFolderPath = "";
#-------------------------------DO NOT EDIT UNDER THIS LINE------------------------------- 
Import-Module (Join-Path $NSTFolderPath "Microsoft.Dynamics.Nav.Management.dll");

$NewUserParams = @{
    ServerInstance = $ServerInstance
    WindowsAccount = $WindowsAccount
    ProfileId = $ProfileId
}

if ($Tenant) {
    $NewUserParams.Tenant = $Tenant
}

New-NAVServerUser @NewUserParams

SQL

The commands for inserting through the SQL server is a little different based on the Dynamics NAV version

info

Before running the script please fill out the variables

• {DATABASE_NAME}
• {DOMAIN\UserID}
• {USERSID} some times you can't get the USER SID through SSMS. If you can't get it from SSMS then use Command Prompt/Get current User SID

2013

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])
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

2015

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])
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], [Authentication Object ID])
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

2017

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])
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

BC14

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