SQL Server - Find all permissions/access for user(s)/login(s) for all databases on a server

Question

Case:

• User is leaving the organization and I'd like to see all security for this person for all databases on a server.

Background:

• A question like this for just one database has been asked.
• This question is different from this question in that it asks to find the security in all databases, rather than just one. Also, this asks for the ability to specify a specific user and or a specific login.
• The code below is from Sean Rose's answer as it modified the approved answer with some improvements.
• I added variable names and the functionality to be able to search for a login and or user specifically, or you can leave the variables blank.

Desired Modifications:

• I would like this to be modified to include all databases on a server.

What I don't know is how to look at all databases on a server.

            DECLARE @DatabaseUserName VARCHAR(50)  -- ='user'
          , @LoginName        VARCHAR(50)  -- ='login'



    SELECT      [UserType]         = CASE princ.[type] WHEN 'S' THEN 'SQL User'
                                         WHEN 'U' THEN 'Windows User'
                                         WHEN 'G' THEN 'Windows Group' END
              , [DatabaseUserName] = princ.[name]
              , [LoginName]        = ulogin.[name]
              , [Role]             = NULL
              , [PermissionType]   = perm.[permission_name]
              , [PermissionState]  = perm.[state_desc]
              , [ObjectType]       = CASE perm.[class] WHEN 1 THEN obj.[type_desc] -- Schema-contained objects
                                         ELSE perm.[class_desc]                    -- Higher-level objects
                                     END
              , [Schema]           = objschem.[name]
              , [ObjectName]       = CASE perm.[class] WHEN 3 THEN permschem.[name] -- Schemas
                                         WHEN 4 THEN imp.[name]                     -- Impersonations
                                         ELSE OBJECT_NAME (perm.[major_id])         -- General objects
                                     END
              , [ColumnName]       = col.[name]
    FROM
        --Database user
                sys.database_principals  AS princ
    --Login accounts
    LEFT JOIN   sys.server_principals    AS ulogin ON ulogin.[sid] = princ.[sid]
    --Permissions
    LEFT JOIN   sys.database_permissions AS perm ON perm.[grantee_principal_id] = princ.[principal_id]
    LEFT JOIN   sys.schemas              AS permschem ON permschem.[schema_id] = perm.[major_id]
    LEFT JOIN   sys.objects              AS obj ON obj.[object_id] = perm.[major_id]
    LEFT JOIN   sys.schemas              AS objschem ON objschem.[schema_id] = obj.[schema_id]
    --Table columns
    LEFT JOIN   sys.columns              AS col ON col.[object_id] = perm.[major_id]
                                                   AND   col.[column_id] = perm.[minor_id]
    --Impersonations
    LEFT JOIN   sys.database_principals  AS imp ON imp.[principal_id] = perm.[major_id]
    WHERE       princ.[type] IN ( 'S', 'U', 'G' )
                -- No need for these system accounts
                AND princ.[name] NOT IN ( 'sys', 'INFORMATION_SCHEMA' )
                AND (princ.[name] = @DatabaseUserName OR  @DatabaseUserName IS NULL)            
                AND (ulogin.[name] = @LoginName OR  @LoginName IS NULL)
    UNION

    --2) List all access provisioned to a SQL user or Windows user/group through a database or application role
    SELECT      [UserType]         = CASE membprinc.[type] WHEN 'S' THEN 'SQL User'
                                         WHEN 'U' THEN 'Windows User'
                                         WHEN 'G' THEN 'Windows Group' END
              , [DatabaseUserName] = membprinc.[name]
              , [LoginName]        = ulogin.[name]
              , [Role]             = roleprinc.[name]
              , [PermissionType]   = perm.[permission_name]
              , [PermissionState]  = perm.[state_desc]
              , [ObjectType]       = CASE perm.[class] WHEN 1 THEN obj.[type_desc] -- Schema-contained objects
                                         ELSE perm.[class_desc]                    -- Higher-level objects
                                     END
              , [Schema]           = objschem.[name]
              , [ObjectName]       = CASE perm.[class] WHEN 3 THEN permschem.[name] -- Schemas
                                         WHEN 4 THEN imp.[name]                     -- Impersonations
                                         ELSE OBJECT_NAME (perm.[major_id])         -- General objects
                                     END
              , [ColumnName]       = col.[name]
    FROM
        --Role/member associations
                sys.database_role_members AS members
    --Roles
    JOIN        sys.database_principals   AS roleprinc ON roleprinc.[principal_id] = members.[role_principal_id]
    --Role members (database users)
    JOIN        sys.database_principals   AS membprinc ON membprinc.[principal_id] = members.[member_principal_id]
    --Login accounts
    LEFT JOIN   sys.server_principals     AS ulogin ON ulogin.[sid] = membprinc.[sid]
    --Permissions
    LEFT JOIN   sys.database_permissions  AS perm ON perm.[grantee_principal_id] = roleprinc.[principal_id]
    LEFT JOIN   sys.schemas               AS permschem ON permschem.[schema_id] = perm.[major_id]
    LEFT JOIN   sys.objects               AS obj ON obj.[object_id] = perm.[major_id]
    LEFT JOIN   sys.schemas               AS objschem ON objschem.[schema_id] = obj.[schema_id]
    --Table columns
    LEFT JOIN   sys.columns               AS col ON col.[object_id] = perm.[major_id]
                                                    AND   col.[column_id] = perm.[minor_id]
    --Impersonations
    LEFT JOIN   sys.database_principals   AS imp ON imp.[principal_id] = perm.[major_id]
    WHERE       membprinc.[type] IN ( 'S', 'U', 'G' )
                -- No need for these system accounts
                AND membprinc.[name] NOT IN ( 'sys', 'INFORMATION_SCHEMA' )
                AND (membprinc.[name] = @DatabaseUserName OR  @DatabaseUserName IS NULL)
                AND (ulogin.[name] = @LoginName OR  @LoginName IS NULL)
    UNION

    --3) List all access provisioned to the public role, which everyone gets by default
    SELECT      [UserType]         = '{All Users}'
              , [DatabaseUserName] = '{All Users}'
              , [LoginName]        = '{All Users}'
              , [Role]             = roleprinc.[name]
              , [PermissionType]   = perm.[permission_name]
              , [PermissionState]  = perm.[state_desc]
              , [ObjectType]       = CASE perm.[class] WHEN 1 THEN obj.[type_desc] -- Schema-contained objects
                                         ELSE perm.[class_desc]                    -- Higher-level objects
                                     END
              , [Schema]           = objschem.[name]
              , [ObjectName]       = CASE perm.[class] WHEN 3 THEN permschem.[name] -- Schemas
                                         WHEN 4 THEN imp.[name]                     -- Impersonations
                                         ELSE OBJECT_NAME (perm.[major_id])         -- General objects
                                     END
              , [ColumnName]       = col.[name]
    FROM
        --Roles
                sys.database_principals  AS roleprinc
    --Role permissions
    LEFT JOIN   sys.database_permissions AS perm ON perm.[grantee_principal_id] = roleprinc.[principal_id]
    LEFT JOIN   sys.schemas              AS permschem ON permschem.[schema_id] = perm.[major_id]
    --All objects
    JOIN        sys.objects              AS obj ON obj.[object_id] = perm.[major_id]
    LEFT JOIN   sys.schemas              AS objschem ON objschem.[schema_id] = obj.[schema_id]
    --Table columns
    LEFT JOIN   sys.columns              AS col ON col.[object_id] = perm.[major_id]
                                                   AND   col.[column_id] = perm.[minor_id]
    --Impersonations
    LEFT JOIN   sys.database_principals  AS imp ON imp.[principal_id] = perm.[major_id]
    WHERE       roleprinc.[type] = 'R'
                AND roleprinc.[name] = 'public'
                AND obj.[is_ms_shipped] = 0
                AND (roleprinc.[name] = @DatabaseUserName OR  @DatabaseUserName IS NULL)


    ORDER BY    [UserType]
              , [DatabaseUserName]
              , [LoginName]
              , [Role]
              , [Schema]
              , [ObjectName]
              , [ColumnName]
              , [PermissionType]
              , [PermissionState]
              , [ObjectType];

Answer 1

I can think of two ways you could achieve this.

The first method would be to use a temporary table and execute your permissions script against all databases, like this:

DECLARE @DatabaseName VARCHAR(50);
DECLARE @SqlCommand NVARCHAR(MAX);
DECLARE @DatabaseUserName VARCHAR(50); -- ='user'
DECLARE @LoginName VARCHAR(50); -- ='login'

CREATE TABLE #TEMP_OVERVIEW
(
  DatabaseName     VARCHAR(128)  NOT NULL
, UserType         VARCHAR(13)   NULL
, DatabaseUserName NVARCHAR(128) NOT NULL
, LoginName        NVARCHAR(128) NULL
, Role             NVARCHAR(128) NULL
, PermissionType   NVARCHAR(128) NULL
, PermissionState  NVARCHAR(60)  NULL
, ObjectType       NVARCHAR(60)  NULL
, [Schema]         sys.sysname   NULL
, ObjectName       NVARCHAR(128) NULL
, ColumnName       sys.sysname   NULL
);

DECLARE db_cursor CURSOR LOCAL FAST_FORWARD FOR
SELECT
      name
FROM  master.sys.databases
WHERE name NOT IN ('master', 'msdb', 'model', 'tempdb')
      AND state_desc = 'online';

OPEN db_cursor;

FETCH NEXT FROM db_cursor
INTO
  @DatabaseName;

WHILE @@FETCH_STATUS = 0
BEGIN
  SELECT
    @SqlCommand = N'USE ' + @DatabaseName + N';' + N'
INSERT INTO #TEMP_OVERVIEW
SELECT '''        + @DatabaseName + N''', t.*
FROM (

...Insert your script here...

) AS t
' ;

  EXEC sp_executesql @SqlCommand;

  FETCH NEXT FROM db_cursor
  INTO
    @DatabaseName;
END;

CLOSE db_cursor;
DEALLOCATE db_cursor;

SELECT
      *
FROM  #TEMP_OVERVIEW
WHERE DatabaseUserName = @DatabaseUserName
      AND LoginName = @LoginName;

DROP TABLE #TEMP_OVERVIEW;

The second method is the use of sp_MSforeachdb. But before explaining this furter I must caution you because it is a global cursor and considered to deprecated by Microsoft for many years now. It is also undocumented/unsupported so if you screw up something using sp_MSforeachdb, you're on your own. For example, using:

EXEC sp_MSforeachdb 'USE ?; SELECT ''?'' AS DATABASE_NAME, * FROM INFORMATION_SCHEMA.TABLES'

This would give you:

And so forth for all databases on your server, so as you can see it would be very possible to incorporate your permissions script in a sp_MSforeachdb call.

Answer 2

To get this to run on multiple servers, I ended up using a server group in registered servers, and starting a new query from there, as shown in this video. This enabled me to avoid using a cursor, though I am grateful for Thalio's answer.