You are Here
Creating users in a restored database
Dynamics NAV Featured Installation

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


Post Views: 2,383
Share
Facebook Twitter Pinterest Linkedin

Related Posts

148 thoughts on “Creating users in a restored database”

  5. the best adult generator long term ai companion create erotic videos, images, and virtual characters. flexible settings, high quality, instant results, and easy operation right in your browser. the best features for porn generation.

  10. Нужен проектор? http://projector24.ru большой выбор моделей для дома, офиса и бизнеса. Проекторы для кино, презентаций и обучения, официальная гарантия, консультации специалистов, гарантия качества и удобные условия покупки.

  14. Играешь в казино? ап икс скачать Слоты, рулетка, покер и live-дилеры, простой интерфейс, стабильная работа сайта и возможность играть онлайн без сложных настроек.

  15. Лучшее казино ап икс официальный играйте в слоты и live-казино без лишних сложностей. Простой вход, удобный интерфейс, стабильная платформа и широкий выбор игр для отдыха и развлечения.

  16. Лучшее казино ап икс играйте в слоты и live-казино без лишних сложностей. Простой вход, удобный интерфейс, стабильная платформа и широкий выбор игр для отдыха и развлечения.

  34. Здравствуйте дорогие друзья! На первом этапе нужно разобраться — крыша производственного цеха. Хочешь качественно — вот проверенную компанию: https://montazh-membrannoj-krovli-spb.ru. В большинстве случаев большие площади — требуют особого подхода. Короче, сложная конфигурация — соответственно важен опыт бригады. Мы используем профессиональное оборудование. Вот потому что это работает. Что в итоге: это отличные параметры.

  69. For those seeking an exceptional online gaming experience, us.com](https://maxispin.us.com/) stands out as a premier destination. At Maxispin Casino, players can enjoy a vast array of pokies, table games, and other thrilling options, all accessible in both demo and real-money modes. The casino offers attractive bonuses, including free spins and a generous welcome offer, along with cashback promotions and engaging tournaments. To ensure a seamless experience, Maxispin provides various payment methods, efficient withdrawal processes, and reliable customer support through live chat. Security is a top priority, with robust safety measures and a strong focus on responsible gambling tools. Players can easily navigate the site, with detailed guides on account creation, verification, and payment methods. Whether you’re interested in high RTP slots, hold and win pokies, or the latest slot releases, Maxispin Casino delivers a user-friendly and secure platform. Explore their terms and conditions, read reviews, and discover why many consider Maxispin a legitimate and trustworthy choice in Australia.
    The platform provides state-of-the-art tools to create unique and captivating text content.

    **Features of MaxiSpin.us.com**
    MaxiSpin.us.com boasts the ability to generate content in multiple languages as one of its key features.

    **Benefits of Using MaxiSpin.us.com**
    With MaxiSpin.us.com, creating compelling content has never been easier or more efficient.

Leave a Reply

Your email address will not be published. Required fields are marked *