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:
Generate scripts for a specific database:
EXEC dbo.GenerateUserRoleScripts @DatabaseName = 'B151';
- Execute the stored scripts for a specific database:
Wonderful.. Thanks a ton for the article.
ReplyDelete