How to save and then restore permissions after refreshing a database using T-sql

    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