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.

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;