T-sql – List all users and permission

I don't have a full script for the task. But maybe this script could be a start.

It returns a list of logins that don't have permissions granted. Using sp_msforeachdb it searches on all databases using sys.database_principals and sys.database_role_members. You could insert the result in a temp table and then do whatever you need to do with that list.

I'm sure I found this script some time ago somewhere and made some small modifications to fit my needs. But can't find where I found the original one. So sorry if no link provided to the author(s).

SET NOCOUNT ON
CREATE TABLE #all_users (db       VARCHAR(70),
                        SID      VARBINARY(85),
                        stat     VARCHAR(50))
EXEC MASTER.sys.sp_msforeachdb
     'INSERT INTO #all_users  
         SELECT ''?'', CONVERT(varbinary(85), sid) , 
          CASE WHEN  r.role_principal_id IS NULL AND p.major_id IS NULL 
          THEN ''no_db_permissions''  ELSE ''db_user'' END
         FROM [?].sys.database_principals u LEFT JOIN [?].sys.database_permissions p 
           ON u.principal_id = p.grantee_principal_id  
           AND p.permission_name <> ''CONNECT''
          LEFT JOIN [?].sys.database_role_members r 
           ON u.principal_id = r.member_principal_id
          WHERE u.SID IS NOT NULL AND u.type_desc <> ''DATABASE_ROLE'''

IF EXISTS 
   (SELECT l.name
       FROM   sys.server_principals l
              LEFT JOIN sys.server_permissions p
                   ON  l.principal_id = p.grantee_principal_id
                   AND p.permission_name <> 'CONNECT SQL'
              LEFT JOIN sys.server_role_members r
                   ON  l.principal_id = r.member_principal_id
              LEFT JOIN #all_users u
                   ON  l.sid = u.sid
       WHERE  r.role_principal_id IS NULL
              AND l.type_desc <> 'SERVER_ROLE'
              AND p.major_id IS NULL
   )
BEGIN
    SELECT DISTINCT l.name     LoginName,
           l.type_desc,
           l.is_disabled,
           ISNULL(u.stat + ', but is user in ' + u.db + ' DB', 'no_db_users') 
           db_perms,
           CASE 
                WHEN p.major_id IS NULL AND r.role_principal_id IS NULL THEN 
                     'no_srv_permissions'
                ELSE 'na'
           END                 srv_perms
    FROM   sys.server_principals l
           LEFT JOIN sys.server_permissions p
                ON  l.principal_id = p.grantee_principal_id
                AND p.permission_name <> 'CONNECT SQL'
           LEFT JOIN sys.server_role_members r
                ON  l.principal_id = r.member_principal_id
           LEFT JOIN #all_users u
                ON  l.sid = u.sid
    WHERE  l.type_desc <> 'SERVER_ROLE'
           AND ((u.db IS NULL
                       AND p.major_id IS NULL
                       AND r.role_principal_id IS NULL
                 )
                   OR (u.stat = 'no_db_permissions'
                          AND p.major_id IS NULL
                          AND r.role_principal_id IS NULL
                      ))
    ORDER BY 1, 4
END
DROP TABLE #all_users

Sample result:

LoginName            type_desc  is_disabled         db_perms                                        srv_perms
app_agentjobreader   SQL_LOGIN      0       no_db_permissions, but is user in mydb DB           no_srv_permissions
app_web_general      SQL_LOGIN      0       no_db_permissions, but is user in mydb_second DB    no_srv_permissions
app_web_maker        SQL_LOGIN      0       no_db_permissions, but is user in mydb_third DB     no_srv_permissions

One way to accomplish this is:

USE tempdb;
CREATE TABLE dbo.PersonHierarchy
(
    PersonHierarchyID HierarchyID
);

INSERT INTO dbo.PersonHierarchy VALUES ('/1/');
INSERT INTO dbo.PersonHierarchy VALUES ('/1/1/');
INSERT INTO dbo.PersonHierarchy VALUES ('/1/2/');
INSERT INTO dbo.PersonHierarchy VALUES ('/2/');
INSERT INTO dbo.PersonHierarchy VALUES ('/2/1/');

SELECT *
FROM dbo.PersonHierarchy;

DECLARE @T TABLE 
(
    PersonHierarchyID HierarchyID
);

INSERT INTO @T
VALUES ('/1/'), ('/2/');

WITH parent AS (
    SELECT p.PersonHierarchyID 
    FROM dbo.PersonHierarchy p
        INNER JOIN @T t ON p.PersonHierarchyID = t.PersonHierarchyID
)
SELECT ph.PersonHierarchyID.ToString(), parent.PersonHierarchyID.ToString() /* select only the necessary columns, do NOT use SELECT *  */
FROM dbo.PersonHierarchy ph
    INNER JOIN parent ON ph.PersonHierarchyID.IsDescendantOf(parent.PersonHierarchyID) = 1
ORDER BY ph.PersonHierarchyID; /* always include an ORDER BY */