Return to Answer

Marcello, if you absolutely have to be able to achieve your goal with a single script, you could try iterating through the databases and running TSQL in the context of each database. An example is below with sp_executesql running sp_executesql from/in each database in the cursor loop. Note that @Tsql is static here, so it's declared outside of the cursor loop. However it could just as easily be built dynamically within the loop. This example isn't easy. It might even seem a little convoluted. I'll let you be the judge.

Marcello, if you absolutely have to be able to achieve your goal with a single script, you could try iterating through the databases and running TSQL in the context of each database. An example is below with sp_executesql running sp_executesql from/in each database in the cursor loop. Note that @Tsql is static here, so it's declared outside of the cursor loop. However it could just as easily be built dynamically within the loop. This approach isn't easy. It might even seem a little convoluted. I'll let you be the judge.

Marcello, if you absolutely have to be able to achieve your goal with a single script, you could try iterating through the databases and building a TSQL string to be run in the context of each database. Then execute sp_executesql, which executes sp_executesql in the context of the specific database. An example is below. It's not easy, maybe even a little convoluted. I'll let you be the judge.

Marcello, if you absolutely have to be able to achieve your goal with a single script, you could try iterating through the databases and running TSQL in the context of each database. An example is below with sp_executesql running sp_executesql from/in each database in the cursor loop. Note that @Tsql is static here, so it's declared outside of the cursor loop. However it could just as easily be built dynamically within the loop. This example isn't easy. It might even seem a little convoluted. I'll let you be the judge.

DECLARE @Tsql NVARCHAR(MAX)
DECLARE @DB SYSNAME

DECLARE curDB CURSOR FOR
    SELECT d.name
    FROM master.sys.databases d
    --Comment out/adjust this WHERE clause as needed.
    WHERE d.name NOT IN ('master','tempdb')

OPEN curDB
FETCH NEXT FROM curDB INTO @DB, @DBOwner

WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT @DB

    /*
        Build the TSQL statement that you want to run on each database.
        Here is an example that finds database users that are members of
        specific fixed database roles and drops them from those roles.
        Note the single quotes are quadrupled.
    */
    SET @Tsql = '
DECLARE @User SYSNAME, @Role SYSNAME
DECLARE curUsers CURSOR FAST_FORWARD READ_ONLY FOR
    SELECT u.name UserName, r.name dbRole
    FROM sys.database_principals u
    JOIN sys.database_role_members rm
        ON rm.member_principal_id = u.principal_id
    JOIN sys.database_principals r
        ON r.principal_id = rm.role_principal_id
    WHERE r.name IN (''''db_accessadmin'''', ''''db_backupoperator'''', ''''db_securityadmin'''')
    AND u.name NOT IN (''''dbo'''', ''''guest'''', ''''INFORMATION_SCHEMA'''', ''''public'''', ''''sys'''')
    ORDER BY u.name, r.name

OPEN curUsers
FETCH NEXT FROM curUsers INTO @User, @Role

WHILE @@FETCH_STATUS = 0
BEGIN
    EXEC sp_droprolemember @Role, @User
    FETCH NEXT FROM curUsers INTO @User, @Role
END
 
CLOSE curUsers
DEALLOCATE curUsers
'

    --Execute sp_executesql, which executes sp_executesql 
    --in the context of a specific database.
    SET @Tsql = '[' + @DB + ']..sp_executesql N''' + @Tsql + ''''
    EXEC sp_executesql @Tsql;

    FETCH NEXT FROM curDB INTO @DB, @DBOwner
END

CLOSE curDB
DEALLOCATE curDB
 
GO

/*
    Build the TSQL statement that you want to run on each database.
    Here is an example that finds database users that are members of
    specific fixed database roles and drops them from those roles.
    Note the single quotes are quadrupled.
*/
DECLARE @Tsql NVARCHAR(MAX) = '
    DECLARE @User SYSNAME, @Role SYSNAME
    DECLARE curUsers CURSOR FAST_FORWARD READ_ONLY FOR
        SELECT u.name UserName, r.name dbRole
        FROM sys.database_principals u
        JOIN sys.database_role_members rm
            ON rm.member_principal_id = u.principal_id
        JOIN sys.database_principals r
            ON r.principal_id = rm.role_principal_id
        WHERE r.name IN (''''db_accessadmin'''', ''''db_backupoperator'''', ''''db_securityadmin'''')
        AND u.name NOT IN (''''dbo'''', ''''guest'''', ''''INFORMATION_SCHEMA'''', ''''public'''', ''''sys'''')
        ORDER BY u.name, r.name
    
    OPEN curUsers
    FETCH NEXT FROM curUsers INTO @User, @Role
    
    WHILE @@FETCH_STATUS = 0
    BEGIN
        EXEC sp_droprolemember @Role, @User
        FETCH NEXT FROM curUsers INTO @User, @Role
    END
     
    CLOSE curUsers
    DEALLOCATE curUsers
    ';

DECLARE @DB SYSNAME
DECLARE curDB CURSOR FOR
    SELECT d.name
    FROM master.sys.databases d
    --Comment out/adjust this WHERE clause as needed.
    WHERE d.name NOT IN ('master','tempdb')
    
OPEN curDB
FETCH NEXT FROM curDB INTO @DB, @DBOwner
    
WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT @DB
    
    --Execute sp_executesql, which executes sp_executesql 
    --in the context of a specific database.
    SET @Tsql = '[' + @DB + ']..sp_executesql N''' + @Tsql + ''''
    EXEC sp_executesql @Tsql;
    
    FETCH NEXT FROM curDB INTO @DB, @DBOwner
END
    
CLOSE curDB
DEALLOCATE curDB 
GO