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];

What have you tried so far? What problem are you facing? This isn't a free write-my-code or do-my-research service. You don't get to just list some requirements and have someone implement them all, for free, with no effort from you. (Not unless they're pretty trivial, anyway). — ADyson, Jan 27 '20 at 15:51
Hi @ADyson, I've updated the question to reflect that I added the ability to take care of being able to specifically search for a user and or login, or leave them empty. What I'm not sure about yet is how to look at all databases on a server. Thanks for your help. — JM1, Jan 27 '20 at 15:55
Read: **Making a more reliable and flexible sp_MSforeachdb** https://www.mssqltips.com/sqlservertip/2201/making-a-more-reliable-and-flexible-spmsforeachdb/ — David Browne - Microsoft, Jan 27 '20 at 15:56
I believe you can use SSMS for this... assuming you manage permissions manually. If, on the other hand, you're using database permissions as a means of securing access for your application, then there is no fix for being crazy. However, I think this is a good question and deserving of at least one vote. — theMayer, Jan 27 '20 at 16:00
@JM1 great, thanks for clarifying / narrowing down the problem. — ADyson, Jan 27 '20 at 16:01

score 3 · Answer 1 · edited Jun 20 '20 at 09:12

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.

Thank you @Thailo! I may need to use a non-cursor option in my environment. — JM1, Jan 28 '20 at 12:39
Yw, but why is that? You still need something like the first cursor to generate the overview or the second undocumented one to iterate through the list of databases. — Thailo, Jan 28 '20 at 13:26
Administration doesn't typically like the use of cursors in the system. It may be permitted in this case, I would need to check, but I would like to present an alternative in case it wouldn't be if possible. I appreciate the help! — JM1, Jan 28 '20 at 13:52
Indeed. I'm not particularly fond of cursors myself, but I think it's either this or you would need to use something like PowerShell to generate the overall sql script. Either way, I would ask Administration to review this, have them run the script and send over the result. — Thailo, Jan 28 '20 at 14:08

score 1 · Answer 2 · answered Jan 28 '20 at 18:57

1

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.

answered Jan 28 '20 at 18:57

JM1

1,595
5
19
41

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

2 Answers2