How to save and then restore permissions after refreshing a database

The blog post provides a detailed guide on how to save and restore permissions after refreshing a SQL Server database. It introduces stored procedures for capturing and reapplying user and role permissions efficiently, ensuring minimal disruption during a database refresh. This method is particularly helpful when automating database refresh processes.

  • GenerateUserRoleScripts: This procedure generates the SQL scripts to create users and assign roles for the specified database and stores them in the UserRoleScripts table.

  • ExecuteUserRoleScripts: This procedure retrieves the scripts stored in UserRoleScripts and executes them on the specified database.
  • Stored Procedure 1: GenerateUserRoleScripts

    This procedure will generate and store the user-role scripts in the DBA..UserRoleScripts table for the specified database.

    USE DBA;  -- Change the database name as per you're requirement 

    GO

    -- Step 1: Create the procedure to generate and store user-role scripts

    CREATE PROCEDURE dbo.GenerateUserRoleScripts

        @DatabaseName NVARCHAR(128)  -- Input parameter for database name

    AS

    BEGIN

        -- Dynamic SQL to target the specified database

        DECLARE @SQL NVARCHAR(MAX);

        -- Create the UserRoleScripts table if it doesn't exist

        IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[UserRoleScripts]') AND type in (N'U'))

        BEGIN

            CREATE TABLE dbo.UserRoleScripts (

                ID INT IDENTITY(1,1) PRIMARY KEY,

                Script NVARCHAR(MAX),

                GeneratedDate DATETIME DEFAULT GETDATE()

            );

        END;

        -- Generate the scripts for the specified database

        SET @SQL = N'

        INSERT INTO dbo.UserRoleScripts (Script)

        SELECT

            ''IF NOT EXISTS (SELECT 1 FROM sys.database_principals WHERE name = '''''' + mp.name + '''''')'' + CHAR(13) +

            ''BEGIN'' + CHAR(13) +

            ''    CREATE USER ['' + mp.name + ''] FOR LOGIN ['' + mp.name + ''];'' + CHAR(13) +

            ''END;'' + CHAR(13) +

            ''ALTER ROLE ['' + dp.name + ''] ADD MEMBER ['' + mp.name + ''];'' AS Script

        FROM

            [' + @DatabaseName + '].sys.database_role_members drm

        JOIN

            [' + @DatabaseName + '].sys.database_principals dp ON drm.role_principal_id = dp.principal_id

        JOIN

            [' + @DatabaseName + '].sys.database_principals mp ON drm.member_principal_id = mp.principal_id

        WHERE

            dp.name <> ''dbo''  -- Exclude roles where the role is dbo

            AND mp.name <> ''dbo''  -- Exclude users where the user is dbo

        ORDER BY dp.name, mp.name;

        ';

        -- Execute the dynamic SQL

        EXEC sp_executesql @SQL;

    END;

    GO

    Stored Procedure 2: ExecuteUserRoleScripts

    This procedure will fetch and execute the stored scripts from the DBA..UserRoleScripts table.

    USE DBA;  -- change database name as per you're requirement

    GO

    -- Step 2: Create the procedure to execute stored user-role scripts

    CREATE PROCEDURE dbo.ExecuteUserRoleScripts

        @DatabaseName NVARCHAR(128)  -- Input parameter for database name

    AS

    BEGIN

        DECLARE @Script NVARCHAR(MAX);

        -- Declare a cursor to retrieve scripts

        DECLARE ScriptCursor CURSOR FOR

        SELECT Script FROM dbo.UserRoleScripts;

        -- Open the cursor

        OPEN ScriptCursor;

        -- Fetch the first script

        FETCH NEXT FROM ScriptCursor INTO @Script;


        -- Loop through the scripts and execute each one

        WHILE @@FETCH_STATUS = 0

        BEGIN

            -- Execute the retrieved script in the specified database

            SET @Script = 'USE [' + @DatabaseName + ']; ' + @Script;

            EXEC sp_executesql @Script;

            -- Fetch the next script

            FETCH NEXT FROM ScriptCursor INTO @Script;

        END;

        -- Close and deallocate the cursor

        CLOSE ScriptCursor;

        DEALLOCATE ScriptCursor;

    -- Truncate the UserRoleScripts table to remove all records 

      TRUNCATE TABLE dbo.UserRoleScripts;

    END;

    GO

    Usage:

    1. Generate scripts for a specific database:

      EXEC dbo.GenerateUserRoleScripts @DatabaseName = 'B151';

    2. Execute the stored scripts for a specific database:
               EXEC dbo.ExecuteUserRoleScripts @DatabaseName = 'B151';

    1 comment: