Creating users in a restored database

Ever since Microsoft released Dynamics NAV 2013, there has been a discussion on the best way to create a user in a restored database.

The problem is this:

When a SQL backup is made and ported to a different environment, the Windows or Active Directory users that were created in the old environment are not necessarily created in the new environment.

It is always possible to restore the backup on the new SQL server, but that does not give me access to the database.

The easy way is to delete all information in four tables, but that will delete all existing users. If they are not needed then it is no problem, but if we want to keep the existing users then, this is not an option.

Deleting all users can be done running the following script.

USE [Database]
delete from [dbo].[User]
delete from [dbo].[Access Control]
delete from [dbo].[User Property]
delete from [dbo].[Page Data Personalization]
delete from [dbo].[User Default Style Sheet]
delete from [dbo].[User Metadata]
delete from [dbo].[User Personalization]
GO

If the user names are precisely the same just with a different domain then it is possible to “update” the windows security id with this script.

UPDATE User SET [Windows Security ID] = '<new_Windows_security_id>' where [user name] = '<username>';
GO

Combined with the syslogin table and a little SQL magic, it may even be possible to change all users from one domain to another and from one Windows Security id to another.

But what if I want a script to run, that will:

• Create a user on the SQL server
• Generate a random User SID
• Create the user in the database
• Add a role to the user
• Give permission to the user in the new database

In one go.

It took a bit of Googling and help from dailycoding.com, Daniel Rimmelzwaan on Mibuso and other resources to compile this script, which I pass on to you without any guaranties and to serve as inspiration. Just add information on the username, the database and the permissions you want to assign to the user and run the script.

And you are in.

USE [master]

/* Declare variables */
DECLARE @USERSID uniqueidentifier, @USERSIDTXT varchar(50), @PERMISSION varchar(50), @USERNAME varchar(50), @WINDOWSSID varchar(119), @DATABASENAME varchar(50), @BinSid VARBINARY(100), @t nvarchar(4000)
/* Username, database and permissions must be entered here */
SET @USERNAME = 'N4T\invoice'
SET @DATABASENAME = 'N4Tdemo'
SET @PERMISSION = 'SUPER'

/* Generate a random unique id */
SELECT NEWID()
SET @USERSID    = NEWID()
SET @USERSIDTXT = CONVERT(VARCHAR(50), @USERSID)

/* Create Loin if necessary */
BEGIN TRY
	SET @t = N'CREATE LOGIN [' + @USERNAME + '] FROM WINDOWS'
	exec sys.sp_executesql @t 
END TRY 
BEGIN CATCH
END CATCH

/* Get the WindowsSID from the SQL server */ 
SET @BinSid = (SELECT sid FROM syslogins where loginname=@USERNAME)

/* Convert the uniqueidentifier to NAV SID */
IF LEN(@BinSID) % 4 <> 0 raiserror('User Name not found', 20, -1) with log

  DECLARE @i AS INT
  DECLARE @j AS INT

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

  SET @j = 9
  SET @i = LEN(@BinSID)

  WHILE @j < @i
  BEGIN
    DECLARE @val BINARY(4)
    SELECT @val = SUBSTRING(@BinSID, @j, 4)
    SELECT @WINDOWSSID = @WINDOWSSID + '-'
      + CONVERT(VARCHAR, CONVERT(BIGINT, CONVERT(VARBINARY, REVERSE(CONVERT(VARBINARY, @val))))) 
    SET @j = @j + 4
  END   

/* Change database */
SET @t = N'USE [' + @DATABASENAME +']'
exec sys.sp_executesql @t

/* Create user */
SET @t = N'CREATE USER [' +  @USERNAME + '] FOR LOGIN [' + @USERNAME + ']'
exec sys.sp_executesql @t

/* Add role to user */ 
SET @t = N'sp_addrolemember ' + '''' + 'db_owner' + ''''+ ', ' + '''' + @USERNAME + ''''
exec sys.sp_executesql @t

/* Insert user in database */
SET @t = N'INSERT INTO [' + @DATABASENAME +'].[dbo].[User] '+
           '([User Security ID]
           ,[User Name]
           ,[Full Name]
           ,[State]
           ,[Change Password]
           ,[Expiry Date]
           ,[Windows Security ID]
           ,[License Type]
		   ,[Authentication Email])
           
     VALUES
           (' + '''' + @USERSIDTXT + '''' +
           ',' + '''' + @USERNAME + '''' +
           ',' + '''' + '''' +
           ',' + '0' +
           ',' + '0' +
           ',' + '''' + '1753-01-01 00:00:00.000' + '''' +
           ','+ ''''+ @WINDOWSSID + '''' +
           ',' + '0' + 
		   ',' + '''' + '''' +')'
exec sys.sp_executesql @t

/* Add permissions */
SET @t = 'INSERT INTO [' + @DATABASENAME +'].[dbo].[Access Control]
           ([User Security ID]
           ,[Role ID]
           ,[Company Name])
     VALUES
           (' + '''' + @USERSIDTXT + '''' +
           ',' + '''' + @PERMISSION + '''' +
           ',' + ''''  + '''' + ')'
exec sys.sp_executesql @t

/* Add User Property */
SET @t = 'INSERT INTO [' + @DATABASENAME +'].[dbo].[User Property]
           ([User Security ID]
           ,[Password]
           ,[Name Identifier]
           ,[Authentication Key]
           ,[WebServices Key]
           ,[WebServices Key Expiry Date]
		   ,[Authentication Object ID])
     VALUES
           (' + '''' + @USERSIDTXT + '''' +
           ',' + ''''  + '''' + 
           ',' + ''''  + '''' + 
           ',' + ''''  + '''' +
           ',' + ''''  + '''' +
           ',' + '''' + '1753-01-01 00:00:00.000' + '''' + 
		   ',' + '''' + '''' +')'

exec sys.sp_executesql @t
GO