Return to Revisions

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.

/*
    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