Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

SQL Server Permissions List for Read and Write Access for all Databases


By:   |   Updated: 2019-08-22   |   Comments (1)   |   Related: More > Security

Problem

For compliance auditing, a customer asked for a list of users who have read or write access in any database on the SQL Server instance. Although there are several tables that can provide us the information, the permissions can be at the instance level or at the individual database level.  Further, the permissions can be granted through a role, a role that is member of another role or directly to a login or database user, so it requires some time to prepare the report.

Solution

The script below gives you the requested information in a small amount of time. For example, in one server with 25 databases it completed in 14 seconds. When it is run in the whole registered servers through a multi-server query (85 instances and 335 databases), it took 5 minutes and 38 seconds to return 10,372 rows with all the information.

SQL Server Permissions Script

Below is the script.

;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

SQL Server Permissions Script Description

The script works in the following way:

  1. Creates a CTE named "explicit" that contains the server permissions not granted through a role. For this, we inspect the table "server_permissions" for the operations: control server, take ownership, impersonate, administer bulk operations, or alter. Although impersonating is not a write permission, it allows the user to impersonate any user (like SA) and then write to the database. The same occurs for take ownership: it allows the user to take ownership of an object and write to it. We filter only the permissions that were "grant" or "with grant".
  2. For the same CTE "explicit", we search for the users that are members of the previously selected ones, in case any of them was a server role. This will allow us to view what permission were granted and through what intermediate role.
  3. Create a CTE named "fixed" that contains the server permissions granted through a role. For this, we focus on the roles: sysadmin, securityadmin and bulkadmin. Although securityadmin is not a write permission, it allows to modify any user and elevate privileges and then write to the database.
  4. For the same CTE "fixed", we search for the users that are members of the previously selected ones, in case any of them was a server role. This will allow us to view what roles were granted through an intermediate role.
  5. Return the users, the user type, the user creation date, if it is disabled or not, the permission, and if it was granted through an intermediate role. We exclude the system accounts as we’re only interested in users. Note that we set the recursion to 10, just in case there are roles nested several levels deep.
  6. Create a temporary table for the individual database permissions, and create a dynamic command for each online database. This is what it will do for all databases:
    • Create a CTE named "explicit" that contains the database permissions not granted through a role. For this, we inspect the table "database_permissions" for the operations: insert, update, delete, control, administer database bulk operations, impersonate, select, take ownership, alter or create. The select operations are the ones that grant read permissions, and all others grant the write permission. Although impersonating is not a write permission, it allows the user to impersonate any user (like dbo) and then write to the database. The same occurs for take ownership and control: it allows the user to take ownership of an object and write to it. We filter only the permissions that were "grant" or "with grant".
    • For the same CTE "explicit", we search for the users that are members of the previously selected ones, in case any of them was a database role. This will allow us to view what permission were granted and through what intermediate role.
    • Create a CTE named "fixed" that contains the database permissions granted through a role. For this, we focus on the roles: db_owner, db_datareader, db_datawriter, db_ddladmin, db_securityadmin and db_accessadmin. Although db_securityadmin and db_accessadmin are not write permissions, they allow to modify any user and then write to the database.
    • For the same CTE "fixed", we search for the users that are members of the previously selected ones, in case any of them was a database role. This will allow us to view what roles were granted through an intermediate role.
    • Note that we set the recursion to 10, just in case there are roles nested several levels deep.
  7. Return the databases, users, the user type, the user creation date, the permission, and if it was granted through an intermediate role. We exclude the system accounts as we’re only interested in users, and also exclude the system databases but you can include them if you want.

SQL Server Permissions Sample Output

After running the query, we get the following results for the server permissions:

name type_desc create_date is_disabled permission grant_through
BUILTIN\Administrators WINDOWS_GROUP 5/23/2009 0 sysadmin  
monitoring SQL_LOGIN 11/7/2011 0 sysadmin  
NT AUTHORITY\SYSTEM WINDOWS_LOGIN 5/23/2009 0 sysadmin  
##MS_PolicySigning Certificate## CERTIFICATE_MAPPED _LOGIN 2/25/2019 0 CONTROL SERVER  
NT AUTHORITY\SYSTEM WINDOWS_LOGIN 2/25/2019 0 ALTER ANY AVAILABILITY GROUP  
NT AUTHORITY\SYSTEM WINDOWS_LOGIN 4/7/2016 0 ADMINISTER BULK OPERATIONS  
NT AUTHORITY\SYSTEM WINDOWS_LOGIN 4/7/2016 0 ALTER ANY CONNECTION  
NT AUTHORITY\SYSTEM WINDOWS_LOGIN 4/7/2016 0 ALTER ANY CREDENTIAL  
NT AUTHORITY\SYSTEM WINDOWS_LOGIN 4/7/2016 0 ALTER ANY DATABASE  
NT AUTHORITY\SYSTEM WINDOWS_LOGIN 4/7/2016 0 ALTER ANY ENDPOINT  
NT AUTHORITY\SYSTEM WINDOWS_LOGIN 4/7/2016 0 ALTER ANY EVENT NOTIFICATION  
NT AUTHORITY\SYSTEM WINDOWS_LOGIN 4/7/2016 0 ALTER ANY LINKED SERVER  
NT AUTHORITY\SYSTEM WINDOWS_LOGIN 4/7/2016 0 ALTER ANY LOGIN  
NT AUTHORITY\SYSTEM WINDOWS_LOGIN 4/7/2016 0 ALTER ANY SERVER AUDIT  
NT AUTHORITY\SYSTEM WINDOWS_LOGIN 4/7/2016 0 ALTER ANY SERVER ROLE  
NT AUTHORITY\SYSTEM WINDOWS_LOGIN 4/7/2016 0 ALTER RESOURCES  
NT AUTHORITY\SYSTEM WINDOWS_LOGIN 4/7/2016 0 ALTER SERVER STATE  
NT AUTHORITY\SYSTEM WINDOWS_LOGIN 4/7/2016 0 ALTER SETTINGS  
NT AUTHORITY\SYSTEM WINDOWS_LOGIN 4/7/2016 0 ALTER TRACE  
NT AUTHORITY\SYSTEM WINDOWS_LOGIN 4/7/2016 0 CONTROL SERVER  
domain\superuser WINDOWS_LOGIN 1/5/2016 0 securityadmin  
domain\application WINDOWS_LOGIN 1/27/2018 0 bulkadmin  

And the following results for the database permissions:

database username type_desc create_date permission grant_through
master monitoring SQL_USER 3/28/2012 SELECT  
master domain\reporting WINDOWS_USER 2/20/2019 db_datareader  
msdb ##MS_PolicyEvent ProcessingLogin## SQL_USER 2/25/2019 SELECT PolicyAdministratorRole
security application SQL_USER 6/11/2012 db_owner  
security domain\application WINDOWS_USER 12/3/2014 db_datawriter  
master domain\superuser WINDOWS_USER 2/2/2012 CONTROL EXECROLE
master domain\superuser WINDOWS_USER 2/2/2012 CREATE DATABASE EXECROLE
ReportServer domain\serviceaccount WINDOWS_USER 12/22/2011 db_owner RSExecRole
ReportServer domain\serviceaccount WINDOWS_USER 12/22/2011 DELETE RSExecRole
ReportServer domain\serviceaccount WINDOWS_USER 12/22/2011 INSERT RSExecRole
ReportServer domain\serviceaccount WINDOWS_USER 12/22/2011 SELECT RSExecRole
ReportServer domain\serviceaccount WINDOWS_USER 12/22/2011 UPDATE RSExecRole
Next Steps
  • You can learn more about the server permissions in this link.
  • You can learn more about the server fixed roles in this link.
  • You can learn more about the database permissions in this link.
  • You can learn more about the database fixed roles in this link.


Last Updated: 2019-08-22


get scripts

next tip button



About the author
MSSQLTips author Pablo Echeverria I've worked for more than 10 years as a software programmer and analyst. In 2016, I switched jobs to a DBA position, where I have implement new processes, created better monitoring tools and grown my data scientist skills.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Friday, September 20, 2019 - 8:29:35 AM - Piero Salandin Back To Top

Hello.
I got an error code when using the script in SQL Server 2008 R2:

Messaggio 240, livello 16, stato 1, riga 1
Types don't match between the anchor and the recursive part in column "grant_through" of recursive query "explicit".


Learn more about SQL Server tools