Monday, March 14, 2022

Get Full SQL permission List

 ;WITH 

[explicit] AS (

   SELECT [p].[principal_id], [p].[name], [p].[type_desc], [p].[create_date], [p].[is_disabled],

         [dbp].[permission_name] COLLATE SQL_Latin1_General_CP1_CI_AS [permission],

         CAST('' AS SYSNAME) [grant_through]

   FROM [sys].[server_permissions] [dbp]

   INNER JOIN [sys].[server_principals] [p] ON [dbp].[grantee_principal_id] = [p].[principal_id]

   --WHERE ([dbp].[type] IN ('CL','TO','IM','ADBO') OR [dbp].[type] LIKE 'AL%')

   --  AND [dbp].[state] IN ('G','W')

   UNION ALL

   SELECT [dp].[principal_id], [dp].[name], [dp].[type_desc], [dp].[create_date], [dp].[is_disabled], [p].[permission], [p].[name] [grant_through]

   FROM [sys].[server_principals] [dp]

   INNER JOIN [sys].[server_role_members] [rm] ON [rm].[member_principal_id] = [dp].[principal_id]

   INNER JOIN [explicit] [p] ON [p].[principal_id] = [rm].[role_principal_id]

   ),

[fixed] AS (

   SELECT [dp].[principal_id], [dp].[name], [dp].[type_desc], [dp].[create_date], [dp].[is_disabled], [p].[name] [permission], CAST('' AS SYSNAME) [grant_through]

   FROM [sys].[server_principals] [dp]

   INNER JOIN [sys].[server_role_members] [rm] ON [rm].[member_principal_id] = [dp].[principal_id]

   INNER JOIN [sys].[server_principals] [p] ON [p].[principal_id] = [rm].[role_principal_id]

   --WHERE [p].[name] IN ('sysadmin','securityadmin','bulkadmin')

   UNION ALL

   SELECT [dp].[principal_id], [dp].[name], [dp].[type_desc], [dp].[create_date], [dp].[is_disabled], [p].[permission], [p].[name] [grant_through]

   FROM [sys].[server_principals] [dp]

   INNER JOIN [sys].[server_role_members] [rm] ON [rm].[member_principal_id] = [dp].[principal_id]

   INNER JOIN [fixed] [p] ON [p].[principal_id] = [rm].[role_principal_id]

   )

SELECT DISTINCT [name], [type_desc], [create_date], [is_disabled], [permission], [grant_through]

FROM [explicit]

--WHERE [type_desc] NOT IN ('SERVER_ROLE')

--  AND [name] NOT IN ('sa','SQLDBO','SQLNETIQ')

--  AND [name] NOT LIKE '##%'

--  AND [name] NOT LIKE 'NT SERVICE%'

--  AND [name] NOT LIKE 'NT AUTHORITY%'

--  AND [name] NOT LIKE 'BUILTIN%'

UNION ALL

SELECT DISTINCT [name], [type_desc], [create_date], [is_disabled], [permission], [grant_through]

FROM [fixed]

--WHERE [type_desc] NOT IN ('SERVER_ROLE')

--  AND [name] NOT IN ('sa','SQLDBO','SQLNETIQ')

--  AND [name] NOT LIKE '##%'

--  AND [name] NOT LIKE 'NT SERVICE%'

--  AND [name] NOT LIKE 'NT AUTHORITY%'

--  AND [name] NOT LIKE 'BUILTIN%'

ORDER BY 1

OPTION(MAXRECURSION 10)


CREATE TABLE #Info([database] SYSNAME, [username] SYSNAME, [type_desc] NVARCHAR(60), [create_date] DATETIME, [permission] SYSNAME, [grant_through] SYSNAME)

DECLARE @cmd VARCHAR(MAX)

SET @cmd = ''

SELECT @cmd = @cmd + 'INSERT #Info EXEC(''

USE ['+[name]+']

;WITH 

[explicit] AS (

   SELECT [p].[principal_id], [p].[name], [p].[type_desc], [p].[create_date],

         [dbp].[permission_name] COLLATE SQL_Latin1_General_CP1_CI_AS [permission],

         CAST('''''''' AS SYSNAME) [grant_through]

   FROM [sys].[database_permissions] [dbp]

   INNER JOIN [sys].[database_principals] [p] ON [dbp].[grantee_principal_id] = [p].[principal_id]

   WHERE ([dbp].[type] IN (''''IN'''',''''UP'''',''''DL'''',''''CL'''',''''DABO'''',''''IM'''',''''SL'''',''''TO'''') OR [dbp].[type] LIKE ''''AL%'''' OR [dbp].[type] LIKE ''''CR%'''')

     AND [dbp].[state] IN (''''G'''',''''W'''')

   UNION ALL

   SELECT [dp].[principal_id], [dp].[name], [dp].[type_desc], [dp].[create_date], [p].[permission], [p].[name] [grant_through]

   FROM [sys].[database_principals] [dp]

   INNER JOIN [sys].[database_role_members] [rm] ON [rm].[member_principal_id] = [dp].[principal_id]

   INNER JOIN [explicit] [p] ON [p].[principal_id] = [rm].[role_principal_id]

   ),

[fixed] AS (

   SELECT [dp].[principal_id], [dp].[name], [dp].[type_desc], [dp].[create_date], [p].[name] [permission], CAST('''''''' AS SYSNAME) [grant_through]

   FROM [sys].[database_principals] [dp]

   INNER JOIN [sys].[database_role_members] [rm] ON [rm].[member_principal_id] = [dp].[principal_id]

   INNER JOIN [sys].[database_principals] [p] ON [p].[principal_id] = [rm].[role_principal_id]

   --WHERE [p].[name] IN (''''db_owner'''',''''db_datareader'''',''''db_datawriter'''',''''db_ddladmin'''',''''db_securityadmin'''',''''db_accessadmin'''')

   UNION ALL

   SELECT [dp].[principal_id], [dp].[name], [dp].[type_desc], [dp].[create_date], [p].[permission], [p].[name] [grant_through]

   FROM [sys].[database_principals] [dp]

   INNER JOIN [sys].[database_role_members] [rm] ON [rm].[member_principal_id] = [dp].[principal_id]

   INNER JOIN [fixed] [p] ON [p].[principal_id] = [rm].[role_principal_id]

   )

SELECT DB_NAME(), [name], [type_desc], [create_date], [permission], [grant_through]

FROM [explicit]

--WHERE [type_desc] NOT IN (''''DATABASE_ROLE'''')

UNION ALL

SELECT DB_NAME(), [name], [type_desc], [create_date], [permission], [grant_through]

FROM [fixed]

--WHERE [type_desc] NOT IN (''''DATABASE_ROLE'''')

OPTION(MAXRECURSION 10)

'');'

FROM [sys].[databases]

--WHERE [state_desc] = 'ONLINE'

EXEC (@cmd)

SELECT DISTINCT *

FROM #Info

--WHERE [username] NOT IN ('dbo','guest','SQLDBO')

--  AND [username] NOT LIKE '##%'

--  AND [database] NOT IN ('master','model','msdb','tempdb')

ORDER BY 1, 2

DROP TABLE #Info


source 

https://www.mssqltips.com/sqlservertip/6145/sql-server-permissions-list-for-read-and-write-access-for-all-databases/



No comments: