Database users are lost after restore - how to fix

Question

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.

Answer 1

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.

Answer 2

Hi @Anas Tina ,

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.