List Azure SQL Database Read and Write Permissions

,

I wrote an article to List SQL Server read/write permissions, and a user commented it would be nice to get something similar about Azure SQL Database. The query is basically the same as for a single database, but there are several things you need to be aware of:

  • In Azure, when you create the database you must create a server, there you specify the administrator user and password. This will be a server login with permissions in the [master] database which is able to create other server logins. You can view the server logins querying [sys].[sql_logins], there is no table named [sys].[server_permissions].
  • In the table [sys].[server_principals] you won't find the server logins. The table [sys].[server_role_members] is empty.
  • The administrator user doesn't have any server role, and can't assign it to new users.
  • The administrator user can't assign any [db_*] database role in the [master] database, only [dbmanager] and [loginmanager].
  • The administrator user can't create schemas or roles in the [master] database.
  • The administrator user can't assign permissions to other users (or itself) on INFORMATION_SCHEMA or SYS schemas in the [master] database.
  • For each database, including [master], you need to create database logins from server logins. You can view the database logins querying [sys].[sysusers] where [islogin]=1.
  • You can query [sys].[databases] and it contains a list of all of them, but you can't include the "USE [database]" clause in your statements, so the only way to aggregate the results is by connecting individually to each database; this can be done programmatically i.e. using PowerShell.
  • There are fewer types in [sys].[database_permissions], but I've left them from the original script in case some of them are supported in future versions.

Here is the query, which you can run first in the [master] database and then in each individual database:

;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','dbmanager','loginmanager')
   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 DISTINCT DB_NAME() [database], [name] [username], [type_desc], [create_date], [permission], [grant_through]
  FROM [explicit]
 WHERE [type_desc] NOT IN ('DATABASE_ROLE')
UNION ALL
SELECT DISTINCT DB_NAME(), [name], [type_desc], [create_date], [permission], [grant_through]
  FROM [fixed]
 WHERE [type_desc] NOT IN ('DATABASE_ROLE')
ORDER BY 1, 2
OPTION(MAXRECURSION 10);

Here is a sample of the output from the master database, note the administrator user is "pabechevb" and those permissions were already assigned. I manually granted "dbmanager" and "loginmanager" to the "readonlyuser":

databaseusernametype_desccreate_datepermissiongrant_through
masterdboSQL_USER2003-04-08 09:10:42.287db_owner
masterpabechevbSQL_USER2021-04-30 13:09:20.290ALTER
masterpabechevbSQL_USER2021-04-30 13:09:20.290ALTER ANY USER
masterpabechevbSQL_USER2021-04-30 13:09:20.290SELECT
masterreadonlyuserSQL_USER2021-04-30 14:29:21.700dbmanager
masterreadonlyuserSQL_USER2021-04-30 14:29:21.700loginmanager
masterreadonlyuserSQL_USER2021-04-30 14:29:21.700SELECTloginmanager

And here is a sample of the output from an individual database after the "readonlyuser" is granted all permissions:

databaseusernametype_desccreate_datepermissiongrant_through
pabechevbdboSQL_USER2003-04-08 09:10:42.287db_owner
pabechevbreadonlyuserSQL_USER2021-04-30 15:03:59.503ALTER
pabechevbreadonlyuserSQL_USER2021-04-30 15:03:59.503CONTROL
pabechevbreadonlyuserSQL_USER2021-04-30 15:03:59.503DELETE
pabechevbreadonlyuserSQL_USER2021-04-30 15:03:59.503INSERT
pabechevbreadonlyuserSQL_USER2021-04-30 15:03:59.503SELECT
pabechevbreadonlyuserSQL_USER2021-04-30 15:03:59.503TAKE OWNERSHIP
pabechevbreadonlyuserSQL_USER2021-04-30 15:03:59.503UPDATE

Rate

5 (3)

Share

Share

Rate

5 (3)