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_membersWHERE 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 srINNER JOIN sys.database_principals sp ON sp.principal_id = sr.member_principal_idWHERE 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可以在任意库中输入。

SELECTSuser_name(sr.role_principal_id) AS [Role],sp.NAME AS [Principal] FROMsys.server_role_members AS sr INNER JOINsys.server_principals sp ON sp.principal_id = sr.member_principal_id WHEREsr.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')), '') ))

如果觉得《AZURE SQL 数据库/托管实例查询当前模式下用户权限汇总》对你有帮助，请点赞、收藏，并留下你的观点哦！