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


By:   |   Updated: 2019-08-22   |   Comments (8)   |   Related: > 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:

SQL Server Permissions Sample Output

And the following results for the database permissions:

SQL Server Database Permissions
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.





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


Article Last Updated: 2019-08-22

Comments For This Article




Wednesday, June 2, 2021 - 12:43:24 PM - Pablo Echeverria Back To Top (88790)
Hi Marc Scirri, yes this is correct, because we're interested when a user has read access and when has write access, we use DISTINCT. If we're interested in digging onto which specific tables have this permission, then we would remove the DISTINCT and include the object name as well, but remember there are roles that grant you read/write permissions globally.

Tuesday, June 1, 2021 - 5:19:45 PM - Marc Scirri Back To Top (88788)
I am curious as to why we need the DISTINCT when selecting from the database-level permissions object. I did notice that when I leave DISTINCT out, I get some groups with multiple SELECT permissions listed.

Monday, May 10, 2021 - 4:47:15 PM - Pablo Echeverria Back To Top (88667)
Hi TB, done, please see here: https://www.sqlservercentral.com/articles/list-azure-sql-database-read-and-write-permissions

Wednesday, September 9, 2020 - 4:10:49 AM - TB Back To Top (86438)
Would be nice to get something similar on Azure sql.

Tuesday, August 11, 2020 - 10:06:21 AM - Pablo Echeverria Back To Top (86278)
Hi Chata, you're welcome!

Tuesday, August 11, 2020 - 7:25:58 AM - Chata Back To Top (86275)
Thank you so much! This is the exact query I was looking for. Thank you for the explanation of all the steps. Very informative.

Monday, September 23, 2019 - 9:11:48 AM - Pablo Echeverria Back To Top (82544)

Hi Piero Salandin, I don't get any error in SQL Server 2008 R2 SP3 64-bit. Anyway, please check the database type of sys.server_principals.name and sys.database_principals.name. You can change the CAST('' AS SYSNAME) or make a CAST of sys.server_principals.name and sys.database_principals.name.


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

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".



download














get free sql tips
agree to terms