T-SQL: Add group to each user database

Below is an example script which adds a windows domain group to each database, with the db_reader role.

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
DECLARE @DBName NVARCHAR(255);
DECLARE @SQL NVARCHAR(MAX);
DECLARE @GroupName NVARCHAR(255) = 'mydomain\mygroup';
-- Cursor to loop through all user databases
DECLARE db_cursor CURSOR FOR
SELECT name
FROM sys.databases
WHERE name NOT IN ('master', 'tempdb', 'model', 'msdb') -- Exclude system databases
AND state_desc = 'ONLINE'; -- Only include online databases
OPEN db_cursor;
FETCH NEXT FROM db_cursor INTO @DBName;
WHILE @@FETCH_STATUS = 0
BEGIN
-- Construct the SQL to add the group and grant db_datareader role
SET @SQL = N'
USE [' + @DBName + N'];
IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N''' + @GroupName + N''')
BEGIN
CREATE USER [' + @GroupName + N'] FOR LOGIN [' + @GroupName + N'];
END;
EXEC sp_addrolemember N''db_datareader'', [' + @GroupName + N'];
';
-- Execute the dynamic SQL
EXEC sp_executesql @SQL;
-- Fetch the next database
FETCH NEXT FROM db_cursor INTO @DBName;
END;
CLOSE db_cursor;
DEALLOCATE db_cursor;
DECLARE @DBName NVARCHAR(255); DECLARE @SQL NVARCHAR(MAX); DECLARE @GroupName NVARCHAR(255) = 'mydomain\mygroup'; -- Cursor to loop through all user databases DECLARE db_cursor CURSOR FOR SELECT name FROM sys.databases WHERE name NOT IN ('master', 'tempdb', 'model', 'msdb') -- Exclude system databases AND state_desc = 'ONLINE'; -- Only include online databases OPEN db_cursor; FETCH NEXT FROM db_cursor INTO @DBName; WHILE @@FETCH_STATUS = 0 BEGIN -- Construct the SQL to add the group and grant db_datareader role SET @SQL = N' USE [' + @DBName + N']; IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N''' + @GroupName + N''') BEGIN CREATE USER [' + @GroupName + N'] FOR LOGIN [' + @GroupName + N']; END; EXEC sp_addrolemember N''db_datareader'', [' + @GroupName + N']; '; -- Execute the dynamic SQL EXEC sp_executesql @SQL; -- Fetch the next database FETCH NEXT FROM db_cursor INTO @DBName; END; CLOSE db_cursor; DEALLOCATE db_cursor;
DECLARE @DBName NVARCHAR(255);
DECLARE @SQL NVARCHAR(MAX);
DECLARE @GroupName NVARCHAR(255) = 'mydomain\mygroup';

-- Cursor to loop through all user databases
DECLARE db_cursor CURSOR FOR
SELECT name
FROM sys.databases
WHERE name NOT IN ('master', 'tempdb', 'model', 'msdb') -- Exclude system databases
AND state_desc = 'ONLINE'; -- Only include online databases

OPEN db_cursor;
FETCH NEXT FROM db_cursor INTO @DBName;

WHILE @@FETCH_STATUS = 0
BEGIN
    -- Construct the SQL to add the group and grant db_datareader role
    SET @SQL = N'
    USE [' + @DBName + N'];
    IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N''' + @GroupName + N''')
    BEGIN
        CREATE USER [' + @GroupName + N'] FOR LOGIN [' + @GroupName + N'];
    END;
    EXEC sp_addrolemember N''db_datareader'', [' + @GroupName + N'];
    ';

    -- Execute the dynamic SQL
    EXEC sp_executesql @SQL;

    -- Fetch the next database
    FETCH NEXT FROM db_cursor INTO @DBName;
END;

CLOSE db_cursor;
DEALLOCATE db_cursor;
Share your love