Running a large T-SQL query on all databases

Question

I'm trying to audit database permissions on a whole server. I have a query that produces the output I want, but I need to run it against all databases.

Most solutions appear to use the following:

DECLARE @command varchar(1000)
SELECT @command = 'USE ?; SQL QUERY HERE'
EXEC sp_MSforeachdb @command

However this fails with an error, which doesn't occur when I run the query on the DB myself. I assume it's something to do with being stored in a variable, but it also fails using this format:

EXECUTE sp_MSForEachDB 
'USE ?; SQL QUERY HERE'

The full query I'm trying to run is:

SELECT
   ServerName          = @@SERVERNAME,
   LoginName           = AccessSummary.LoginName,
   LoginType           = CASE WHEN syslogins.isntuser = 1 THEN 'WINDOWS_LOGIN' WHEN syslogins.isntgroup = 1 THEN 'WINDOWS_GROUP' ELSE 'SQL_USER' END,
   DatabaseName        = DB_NAME(),
   SelectAccess        = MAX(AccessSummary.SelectAccess),
   InsertAccess        = MAX(AccessSummary.InsertAccess),
   UpdateAccess        = MAX(AccessSummary.UpdateAccess),
   DeleteAccess        = MAX(AccessSummary.DeleteAccess),
   DBOAccess           = MAX(AccessSummary.DBOAccess),
   SysadminAccess      = MAX(AccessSummary.SysadminAccess)
FROM
   (
       /* Get logins with permissions */
       SELECT
           LoginName           = sysDatabasePrincipal.name,
           SelectAccess        = CASE WHEN permission_name = 'SELECT' THEN 1 ELSE 0 END,
           InsertAccess        = CASE WHEN permission_name = 'INSERT' THEN 1 ELSE 0 END,
           UpdateAccess        = CASE WHEN permission_name = 'UPDATE' THEN 1 ELSE 0 END,
           DeleteAccess        = CASE WHEN permission_name = 'DELETE' THEN 1 ELSE 0 END,
           DBOAccess           = 0,
           SysadminAccess      = 0
       FROM sys.database_permissions AS sysDatabasePermission
       INNER JOIN sys.database_principals AS sysDatabasePrincipal
           ON sysDatabasePrincipal.principal_id = sysDatabasePermission.grantee_principal_id
       INNER JOIN sys.server_principals AS sysServerPrincipal
           ON sysServerPrincipal.sid = sysDatabasePrincipal.sid
       WHERE sysDatabasePermission.class_desc = 'OBJECT_OR_COLUMN'
           AND sysDatabasePrincipal.type_desc IN ('WINDOWS_LOGIN', 'WINDOWS_GROUP', 'SQL_USER')
           AND sysServerPrincipal.is_disabled = 0
       UNION ALL
       /* Get group members with permissions */
       SELECT
           LoginName           = sysDatabasePrincipalMember.name,
           SelectAccess        = CASE WHEN permission_name = 'SELECT' THEN 1 ELSE 0 END,
           InsertAccess        = CASE WHEN permission_name = 'INSERT' THEN 1 ELSE 0 END,
           UpdateAccess        = CASE WHEN permission_name = 'UPDATE' THEN 1 ELSE 0 END,
           DeleteAccess        = CASE WHEN permission_name = 'DELETE' THEN 1 ELSE 0 END,
           DBOAccess           = 0,
           SysadminAccess      = 0
       FROM sys.database_permissions AS sysDatabasePermission
       INNER JOIN sys.database_principals AS sysDatabasePrincipalRole
           ON sysDatabasePrincipalRole.principal_id = sysDatabasePermission.grantee_principal_id
       INNER JOIN sys.database_role_members AS sysDatabaseRoleMember
           ON sysDatabaseRoleMember.role_principal_id = sysDatabasePrincipalRole.principal_id
       INNER JOIN sys.database_principals AS sysDatabasePrincipalMember
           ON sysDatabasePrincipalMember.principal_id = sysDatabaseRoleMember.member_principal_id
       INNER JOIN sys.server_principals AS sysServerPrincipal
           ON sysServerPrincipal.sid = sysDatabasePrincipalMember.sid
       WHERE sysDatabasePermission.class_desc = 'OBJECT_OR_COLUMN'
           AND sysDatabasePrincipalRole.type_desc = 'DATABASE_ROLE'
           AND sysDatabasePrincipalRole.name <> 'public'
           AND sysDatabasePrincipalMember.type_desc IN ('WINDOWS_LOGIN', 'WINDOWS_GROUP', 'SQL_USER')
           AND sysServerPrincipal.is_disabled = 0
       UNION ALL
       /* Get users in db_owner, db_datareader and db_datawriter */
       SELECT
           LoginName           = sysServerPrincipal.name,
           SelectAccess        = CASE WHEN sysDatabasePrincipalRole.name IN ('db_owner', 'db_datareader') THEN 1 ELSE 0 END,
           InsertAccess        = CASE WHEN sysDatabasePrincipalRole.name IN ('db_owner', 'db_datawriter') THEN 1 ELSE 0 END,
           UpdateAccess        = CASE WHEN sysDatabasePrincipalRole.name IN ('db_owner', 'db_datawriter') THEN 1 ELSE 0 END,
           DeleteAccess        = CASE WHEN sysDatabasePrincipalRole.name IN ('db_owner', 'db_datawriter') THEN 1 ELSE 0 END,
           DBOAccess           = CASE WHEN sysDatabasePrincipalRole.name = 'db_owner' THEN 1 ELSE 0 END,
           SysadminAccess      = 0
       FROM sys.database_principals AS sysDatabasePrincipalRole
       INNER JOIN sys.database_role_members AS sysDatabaseRoleMember
           ON sysDatabaseRoleMember.role_principal_id = sysDatabasePrincipalRole.principal_id
       INNER JOIN sys.database_principals AS sysDatabasePrincipalMember
           ON sysDatabasePrincipalMember.principal_id = sysDatabaseRoleMember.member_principal_id
       INNER JOIN sys.server_principals AS sysServerPrincipal
           ON sysServerPrincipal.sid = sysDatabasePrincipalMember.sid
       WHERE sysDatabasePrincipalRole.name IN ('db_owner', 'db_datareader', 'db_datawriter')
           AND sysServerPrincipal.type_desc IN ('WINDOWS_LOGIN', 'WINDOWS_GROUP', 'SQL_LOGIN')
           AND sysServerPrincipal.is_disabled = 0
       UNION ALL
       /* Get users in sysadmin */
       SELECT
           LoginName           = sysServerPrincipalMember.name,
           SelectAccess        = 1,
           InsertAccess        = 1,
           UpdateAccess        = 1,
           DeleteAccess        = 1,
           DBOAccess           = 0,
           SysadminAccess      = 1
       FROM sys.server_principals AS sysServerPrincipalRole
       INNER JOIN sys.server_role_members AS sysServerRoleMember
           ON sysServerRoleMember.role_principal_id = sysServerPrincipalRole.principal_id
       INNER JOIN sys.server_principals AS sysServerPrincipalMember
           ON sysServerPrincipalMember.principal_id = sysServerRoleMember.member_principal_id
       WHERE sysServerPrincipalMember.type_desc IN ('WINDOWS_LOGIN', 'WINDOWS_GROUP', 'SQL_LOGIN')
           AND sysServerPrincipalMember.is_disabled = 0
   ) AS AccessSummary
INNER JOIN MASTER.dbo.syslogins AS syslogins
   ON syslogins.loginname = AccessSummary.LoginName
WHERE AccessSummary.LoginName NOT IN ('NT SERVICE\MSSQLSERVER', 'NT AUTHORITY\SYSTEM', 'NT SERVICE\SQLSERVERAGENT')
GROUP BY
   AccessSummary.LoginName,
   CASE WHEN syslogins.isntuser = 1 THEN 'WINDOWS_LOGIN' WHEN syslogins.isntgroup = 1 THEN 'WINDOWS_GROUP' ELSE 'SQL_USER' END

and I get this error (several times):

Msg 102, Level 15, State 1, Line 35
Incorrect syntax near 'THEN'.

Possible duplicate of [How to run the same query on all the databases on an instance?](http://stackoverflow.com/questions/18462410/how-to-run-the-same-query-on-all-the-databases-on-an-instance) — dfundako, May 25 '16 at 15:09
Msg 102, Level 15, State 1, Line 35 Incorrect syntax near 'THEN'. Msg 102, Level 15, State 1, Line 35 Incorrect syntax near 'THEN'. That highlights : /* Get group members with permissions */ SELECT Msg 102, Level 15, State 1, Line 35 Incorrect syntax near 'THEN'. Msg 102, Level 15, State 1, Line 35 Incorrect syntax near 'THEN'. Msg 102, Level 15, State 1, Line 35 Incorrect syntax near 'THEN'. — 6502peeker, May 25 '16 at 15:20
sp_MSForEachDB only works for strings of 2000 characters and under - could this be the issue? A good way of getting round this is implementing an alternative proc like I did http://codereview.stackexchange.com/questions/117386/replacement-for-sp-msforeachdb — Chris J, May 25 '16 at 15:22

score 2 · Accepted Answer · answered Jun 29 '16 at 09:57

I finally got it working using cursors (which some people seem to hate, but it works). You have to use two single quotes in place of a single quote in the SQL query.

DECLARE @db_name AS nvarchar(max)
DECLARE c_db_names CURSOR FOR
SELECT name
FROM sys.databases
WHERE name NOT IN('master', 'model', 'msdb', 'tempdb')

OPEN c_db_names

FETCH c_db_names INTO @db_name

WHILE @@Fetch_Status = 0
BEGIN
  EXEC('
   USE ' + @db_name + '
   SELECT
   ServerName          = @@SERVERNAME,
   DatabaseName        = DB_NAME(),
   LoginName           = AccessSummary.LoginName,
   LoginType           = CASE WHEN syslogins.isntuser = 1 THEN ''WINDOWS_LOGIN'' WHEN syslogins.isntgroup = 1 THEN ''WINDOWS_GROUP'' ELSE ''SQL_USER'' END,
   SelectAccess        = MAX(AccessSummary.SelectAccess),
   InsertAccess        = MAX(AccessSummary.InsertAccess),
   UpdateAccess        = MAX(AccessSummary.UpdateAccess),
   DeleteAccess        = MAX(AccessSummary.DeleteAccess),
   DBOAccess           = MAX(AccessSummary.DBOAccess),
   SysadminAccess      = MAX(AccessSummary.SysadminAccess)
FROM
   (
       /* Get logins with permissions */
       SELECT
           LoginName           = sysDatabasePrincipal.name,
           SelectAccess        = CASE WHEN permission_name = ''SELECT'' THEN 1 ELSE 0 END,
           InsertAccess        = CASE WHEN permission_name = ''INSERT'' THEN 1 ELSE 0 END,
           UpdateAccess        = CASE WHEN permission_name = ''UPDATE'' THEN 1 ELSE 0 END,
           DeleteAccess        = CASE WHEN permission_name = ''DELETE'' THEN 1 ELSE 0 END,
           DBOAccess           = 0,
           SysadminAccess      = 0
       FROM sys.database_permissions AS sysDatabasePermission
       INNER JOIN sys.database_principals AS sysDatabasePrincipal
           ON sysDatabasePrincipal.principal_id = sysDatabasePermission.grantee_principal_id
       INNER JOIN sys.server_principals AS sysServerPrincipal
           ON sysServerPrincipal.sid = sysDatabasePrincipal.sid
       WHERE sysDatabasePermission.class_desc = ''OBJECT_OR_COLUMN''
           AND sysDatabasePrincipal.type_desc IN (''WINDOWS_LOGIN'', ''WINDOWS_GROUP'', ''SQL_USER'')
           AND sysServerPrincipal.is_disabled = 0
       UNION ALL
       /* Get group members with permissions */
       SELECT
           LoginName           = sysDatabasePrincipalMember.name,
           SelectAccess        = CASE WHEN permission_name = ''SELECT'' THEN 1 ELSE 0 END,
           InsertAccess        = CASE WHEN permission_name = ''INSERT'' THEN 1 ELSE 0 END,
           UpdateAccess        = CASE WHEN permission_name = ''UPDATE'' THEN 1 ELSE 0 END,
           DeleteAccess        = CASE WHEN permission_name = ''DELETE'' THEN 1 ELSE 0 END,
           DBOAccess           = 0,
           SysadminAccess      = 0
       FROM sys.database_permissions AS sysDatabasePermission
       INNER JOIN sys.database_principals AS sysDatabasePrincipalRole
           ON sysDatabasePrincipalRole.principal_id = sysDatabasePermission.grantee_principal_id
       INNER JOIN sys.database_role_members AS sysDatabaseRoleMember
           ON sysDatabaseRoleMember.role_principal_id = sysDatabasePrincipalRole.principal_id
       INNER JOIN sys.database_principals AS sysDatabasePrincipalMember
           ON sysDatabasePrincipalMember.principal_id = sysDatabaseRoleMember.member_principal_id
       INNER JOIN sys.server_principals AS sysServerPrincipal
           ON sysServerPrincipal.sid = sysDatabasePrincipalMember.sid
       WHERE sysDatabasePermission.class_desc = ''OBJECT_OR_COLUMN''
           AND sysDatabasePrincipalRole.type_desc = ''DATABASE_ROLE''
           AND sysDatabasePrincipalRole.name <> ''public''
           AND sysDatabasePrincipalMember.type_desc IN (''WINDOWS_LOGIN'', ''WINDOWS_GROUP'', ''SQL_USER'')
           AND sysServerPrincipal.is_disabled = 0
       UNION ALL
       /* Get users in db_owner, db_datareader and db_datawriter */
       SELECT
           LoginName           = sysServerPrincipal.name,
           SelectAccess        = CASE WHEN sysDatabasePrincipalRole.name IN (''db_owner'', ''db_datareader'') THEN 1 ELSE 0 END,
           InsertAccess        = CASE WHEN sysDatabasePrincipalRole.name IN (''db_owner'', ''db_datawriter'') THEN 1 ELSE 0 END,
           UpdateAccess        = CASE WHEN sysDatabasePrincipalRole.name IN (''db_owner'', ''db_datawriter'') THEN 1 ELSE 0 END,
           DeleteAccess        = CASE WHEN sysDatabasePrincipalRole.name IN (''db_owner'', ''db_datawriter'') THEN 1 ELSE 0 END,
           DBOAccess           = CASE WHEN sysDatabasePrincipalRole.name = ''db_owner'' THEN 1 ELSE 0 END,
           SysadminAccess      = 0
       FROM sys.database_principals AS sysDatabasePrincipalRole
       INNER JOIN sys.database_role_members AS sysDatabaseRoleMember
           ON sysDatabaseRoleMember.role_principal_id = sysDatabasePrincipalRole.principal_id
       INNER JOIN sys.database_principals AS sysDatabasePrincipalMember
           ON sysDatabasePrincipalMember.principal_id = sysDatabaseRoleMember.member_principal_id
       INNER JOIN sys.server_principals AS sysServerPrincipal
           ON sysServerPrincipal.sid = sysDatabasePrincipalMember.sid
       WHERE sysDatabasePrincipalRole.name IN (''db_owner'', ''db_datareader'', ''db_datawriter'')
           AND sysServerPrincipal.type_desc IN (''WINDOWS_LOGIN'', ''WINDOWS_GROUP'', ''SQL_LOGIN'')
           AND sysServerPrincipal.is_disabled = 0
       UNION ALL
       /* Get users in sysadmin */
       SELECT
           LoginName           = sysServerPrincipalMember.name,
           SelectAccess        = 1,
           InsertAccess        = 1,
           UpdateAccess        = 1,
           DeleteAccess        = 1,
           DBOAccess           = 0,
           SysadminAccess      = 1
       FROM sys.server_principals AS sysServerPrincipalRole
       INNER JOIN sys.server_role_members AS sysServerRoleMember
           ON sysServerRoleMember.role_principal_id = sysServerPrincipalRole.principal_id
       INNER JOIN sys.server_principals AS sysServerPrincipalMember
           ON sysServerPrincipalMember.principal_id = sysServerRoleMember.member_principal_id
       WHERE sysServerPrincipalMember.type_desc IN (''WINDOWS_LOGIN'', ''WINDOWS_GROUP'', ''SQL_LOGIN'')
           AND sysServerPrincipalMember.is_disabled = 0
   ) AS AccessSummary
INNER JOIN MASTER.dbo.syslogins AS syslogins
   ON syslogins.loginname = AccessSummary.LoginName
WHERE AccessSummary.LoginName NOT IN (''NT SERVICE\MSSQLSERVER'', ''NT AUTHORITY\SYSTEM'', ''NT SERVICE\SQLSERVERAGENT'')
GROUP BY
   AccessSummary.LoginName,
   CASE WHEN syslogins.isntuser = 1 THEN ''WINDOWS_LOGIN'' WHEN syslogins.isntgroup = 1 THEN ''WINDOWS_GROUP'' ELSE ''SQL_USER'' END')
  FETCH c_db_names INTO @db_name
END

CLOSE c_db_names
DEALLOCATE c_db_names

score 1 · Answer 2 · edited Nov 11 '18 at 21:17

It looks like your query is greater than 2000 characters - try replacing sp_MSforEachDB with the below

CREATE Proc [Process].[ExecForEachDB] ( @cmd NVarchar(Max) )
As /*
Stored Procedure created by Chris Johnson
20th January 2016
The purpose of this stored procedure is to replace the undocumented procedure sp_MSforeachdb as this may be removed in future versions
of SQL Server. The stored procedure iterates through all user databases and executes the code passed to it.
Based off of https://sqlblog.org/2010/02/08/bad-habits-to-kick-relying-on-undocumented-behavior  
*/
    Begin
        Set NoCount On;

    --Declare variables
        Declare @SqlScript NVarchar(Max)= ''
          , @Database NVarchar(257)=''
          , @ErrorMessage NVarchar(Max)='';


    --Test validity, all scripts should contain a "?" to be used in place of a db name
        If @cmd Not Like '%?%'
            Begin
                Set @ErrorMessage = Cast('' As NVarchar(max))
                Set @ErrorMessage = @ErrorMessage+'ExecForEachDB failed, script does not contain the string "?" '
                    + @cmd;

                --If is included as permissions may not be available to create this table
                If Object_Id('[History].[ExecForEachDBLogs]') Is Not Null
                    Begin
                        Insert  [History].[ExecForEachDBErrorLogs]
                                ( [Error] )
                        Values  ( @ErrorMessage );
                    End;

                If Object_Id('[History].[ExecForEachDBLogs]') Is Null
                    Begin
                        Raiserror ('** Warning - Errors are not being logged **',1,1); --if Errors are not being logged raise a low level error
                    End;
                Raiserror (@ErrorMessage,13,1);
            End;

        If @cmd Like '%?%' 
            Begin
    --Use Cursor to hold list of databases to execute against
                Declare [DbNames] Cursor Local Forward_Only Static Read_Only
                For
                    Select  QuoteName([name])
                    From    [sys].[databases]
                    Where   [state] = 0 --online databases
                            And [is_read_only] = 0 --only databases that can be executed against
                            And [database_id] > 4 --only user databases
                            And has_dbaccess([name]) = 1 --only dbs current user has access to
                    Order By [name];

                Open [DbNames];

                Fetch Next From [DbNames] Into @Database; --Get first database to execute against

                While @@fetch_status = 0 --when fetch is successful
                    Begin
                        Set @SqlScript = Cast('' As NVarchar(Max));
                        Set @SqlScript = @SqlScript
                            + Replace(Replace(Replace(@cmd , '?' , @Database) ,
                                              '[[' , '[') , ']]' , ']');--[[ & ]] caused by script including [?]
                        Begin Try 
                            Exec(@SqlScript);
                        End Try
                        Begin Catch --if error happens against any db, raise a high level error advising the database and print the script
                            Set @ErrorMessage = Cast('' As NVarchar(max))
                            Set @ErrorMessage = @ErrorMessage + 'Script failed against database '
                                + @Database;
                            Raiserror (@ErrorMessage,13,1);
                            Print @SqlScript;
                        End Catch;

                        Fetch Next From [DbNames] Into @Database;--Get next database to execute against
                    End;

                Close [DbNames];
                Deallocate [DbNames];
            End;
    End;
GO

score 0 · Answer 3 · answered May 25 '16 at 15:22

DECLARE @sqlCommand VARCHAR(8000)
SET @sqlCommand =
'
USE[?]
IF (db_name() like ''%Filter_DB_Name%'')

BEGIN
DECLARE @sql VARCHAR(1000)
SELECT @sql = ''<SQL Here>''
END
exec (@sql)'
EXEC sp_MSforeachdb @sqlCommand

use Filter_DB_Name in case you don't want to go through all databases in the server where this is running. I usually use db_name() as part of the sql, so I get feedback of the db linked to the particular result

Running a large T-SQL query on all databases

3 Answers3