question

AnasTina-1883 avatar image
0 Votes"
AnasTina-1883 asked CarrinWu-MSFT commented

Database users are lost after restore - how to fix

107275-image.png




We had many facilities that operate in areas where there is poor/no internet connection; the facility staff sent us monthly a backup of the facility database for us to restore it on our data center.

The problem is: when we restore the database all database users and their mappings to logins are lost (restores are done at the data center).

I intend to recreate the database users and map them to server logins after a database restore.

I listed all server logins and their mapped users on a single database [master].dbo.[OurDatabases] that had the simple following schema:


Id | DbName | LoginName | DbUser |



For that purpose, I wrote an algorithm and implemented it using an after insert trigger on [msdb].[dbo].[restorehistory] system database:

 CREATE TRIGGER [dbo].[AfterRestoreDatabase]
     ON [msdb].[dbo].[restorehistory]
 FOR INSERT
 AS
 BEGIN
     DECLARE @DatabaseName AS VARCHAR(500)
    
     SELECT @DatabaseName = destination_database_name FROM INSERTED
        
     IF @DatabaseName IN (SELECT D.DbName FROM [master].dbo.[OurDatabases] D )
     BEGIN
         DECLARE @DbUser AS NVARCHAR(50) = NULL
         SELECT @DbUser = D.DbUser FROM [master].dbo.[OurDatabases] D WHERE D.DbName = @DatabaseName
    
         IF @DbUser IS NULL
         BEGIN
             INSERT INTO DB_Users.dbo.Errors(Descr) VALUES ('Database Restored but no user mapping existed! REASON: UserName not exits in [master].dbo.[OurDatabases].')
         END
         ELSE
         BEGIN
             DECLARE @SQL NVARCHAR(MAX) 
             SET @SQL = 
             '
             USE [' + @DatabaseName + ']
             IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N''' + @DbUser + ''')
                 CREATE USER ' + @DbUser + ' FOR LOGIN ' + @DbUser + '
                
             EXEC sp_addrolemember ''db_owner'', N''' + @DbUser + ''''
    
             EXEC (@SQL)
         END
     END
     ELSE
     BEGIN       
         INSERT INTO DB_Users.dbo.Errors(Descr) VALUES ('Database Restored but no user mapping existed! REASON: Database name not exits in [master].dbo.[OurDatabases].')
     END
 END


The problem is that the trigger does not do the expected on the target database.

sql-server-general
image.png (32.9 KiB)
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @AnasTina-1883, we have not get a reply from you. Did any answers could help you? If there have any answers helped you, please do "Accept Answer". If not, please let us know. By doing so, it will benefit for community members who have this similar issue. Your contribution is highly appreciated. Thank you!

0 Votes 0 ·
ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

A trigger on restorehistory? Now, that's wild! But to be honest, I don't think is the right way to go.

The mapping between database users and server login is by SID, not by name. If these logins are SQL logins, two logins with the same name will typically have different SIDs on two instances. However, CREATE LOGIN accepts SID as an option, so the best is to re-create the login with a matching SID, and in that way, you will never get any mismatches.

Although, what I'm saying here assumes that the use xyz exist only one of these databases. If xyz exists on multiple facilities, this may be more difficult.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

CarrinWu-MSFT avatar image
0 Votes"
CarrinWu-MSFT answered CarrinWu-MSFT edited

Hi @AnasTina-1883,

Welcome to Microsoft Q&A!

Because the SIDs are not mapping. Please refer to this thread to get more information, see below:

  • Logins - Instance level principals that allow an entity to connect to the SQL Server instance. They do not, by their nature, grant any access to databases on the instance. The exception to this is a login with sysadmin rights can use a database because they are sysadmin, but because of sysadmin level permissions.

  • Users - Database level principals that allow an entity to connect to a SQL Server database. Users are associated with logins via SIDs, creating a relationship between the two and allowing a login to connect to the instance and then use the associated user to connect to the database.

And you could get solutions from this blog, it provides two solutions that you could choose.


Best regards,
Carrin


If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.