0

Transactional Replication with No Internet

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.

5
  • 1
    Your reference table contains DatabaseUserName but you seem to be selecting DbUser... Same for DbName... please review the information you are providing. Jun 15 at 13:10
  • 1
    Also, the users comes with the backups, they are a part of the database you just restored. I.e., there's no CREATE USER commando to execute. I agree with the others, we need more information in order to give any meaningful replies. Jun 15 at 14:04
  • I updated the question; would you please help?
    – Anas Tina
    Jun 15 at 21:22
  • if you have distributed datatbses, that aren't always on, you need another aproach. istead of ids use uuids for al data, so that links stay even when you import all data. It will takes some effort, but the benefits a huge
    – nbk
    Jun 15 at 21:44
  • @nbk, would you please provide an answer?
    – Anas Tina
    Jun 15 at 21:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.