AZURE SQL 数据库/托管实例查询当前模式下用户权限汇总

数据库权限的分配与管理，至关重要，不论是云上的PAAS,IAAS或者非云的常规版本数据库，作为DBA都想一目了然快速知道哪些账号或者账号组拥有哪些权限。本文中，我将结合工作中的例子为大家详细列举下。

1. Azure Synapse Analytics[专用 SQL 池(旧称为 "SQL DW")]

情况描述：公司部署了一个数仓，使用Azure Synapse Analytics，现需要查询该数仓中拥有DBOWNER权限的账户/账户组有哪些

SELECT USER_NAME(member_principal_id) AS [Owner]
FROM sys.database_role_members
WHERE USER_NAME(role_principal_id) = 'db_owner'
    AND USER_NAME(member_principal_id) != 'dbo'

2. Azure SQL 托管实例/Azure SQL 数据库

情况描述：公司同时拥有Azure Synapse Analytics/Azure SQL DB/Azure SQL Managed Instance现需要查询在某指定库中哪些用户或者组具有bulkadmin/db_accessadmin/db_securityadmin/db_ddladmin/db_backupoperator/db_owner权限

SELECT user_name(sr.member_principal_id) AS [Principal]
    ,user_name(sr.role_principal_id) AS [Role]
    ,type_desc AS [Principal Type]
FROM sys.database_role_members AS sr
INNER JOIN sys.database_principals sp ON sp.principal_id = sr.member_principal_id
WHERE sr.role_principal_id IN (
        user_id('bulkadmin'),
        user_id('db_accessadmin'),
        user_id('db_securityadmin'),
        user_id('db_ddladmin'),
        user_id('db_backupoperator'))
    OR (sr.role_principal_id = user_id('db_owner')
        AND sr.member_principal_id <> user_id('dbo'))

 

3. Azure SQL各种类型的DB所在实例里

情况描述：公司同时拥有Azure Synapse Analytics/Azure SQL DB/Azure SQL Managed Instance现需要查询拥有固定服务器角色的成员。

SQL可以在任意库中输入。

SELECT
   Suser_name(sr.role_principal_id) AS [Role],
   sp.NAME AS [Principal] 
FROM
   sys.server_role_members AS sr 
   INNER JOIN
      sys.server_principals sp 
      ON sp.principal_id = sr.member_principal_id 
WHERE
   sr.role_principal_id IN 
   (
      Suser_id('sysadmin'),
      suser_id('serveradmin'),
      suser_id('setupadmin'),
      suser_id('processadmin'),
      suser_id('diskadmin'),
      suser_id('dbcreator'),
      suser_id('bulkadmin') 
   )
   AND sp.principal_id != 1 
   AND NOT ( ( sr.role_principal_id = 3 
   AND sp.NAME = 'NT SERVICE\SQLWriter' ) 
   OR 
   (
      sr.role_principal_id = 3 
      AND sp.NAME = 'NT SERVICE\Winmgmt' 
   )
   OR 
   (
      sr.role_principal_id = 3 
      AND sp.NAME = 'NT Service\MSSQLSERVER' 
   )
   OR 
   (
      sr.role_principal_id = 3 
      AND sp.NAME = 'NT SERVICE\SQLSERVERAGENT' 
   )
   OR 
   (
      sr.role_principal_id = 3 
      AND sp.NAME = 'NT Service\SQLIaaSExtension' 
   )
   OR 
   (
      sr.role_principal_id = 3 
      AND sp.NAME = 'NT Service\HealthService' 
   )
   OR 
   (
      sr.role_principal_id = 3 
      AND sp.NAME = 'NT Service\MSSQL' + ISNULL('$' + convert(sysname, serverproperty('InstanceName')), '') 
   )
   OR 
   (
      sr.role_principal_id = 3 
      AND sp.NAME = 'NT SERVICE\SQLAgent' + ISNULL('$' + convert(sysname, serverproperty('InstanceName')), '') 
   )
)