Azure SQL server database 权限管理---查询权限

The role granted to all users by default, has rights to over 1600 objects on my default install of SQL Server 2005 Standard Edition I use in my test lab.  Just how did I come up with that information?  Let's look at the following three system views for our answers.

• sys.database_principals - stores records relating to database principals
  • name - name of the server principal
  • principal_id - id for the principal, used to link to sys.database_permissions
  • type - type of principal (in this case we're specifically interested in Database Role, signified by a value of 'R')
• sys.database_permissions - returns a row for each permission in your SQL Server database
  • class_desc - object type for the permission
  • major_id - ID of the object the permission is granted on foreign key for sys.sysobjects.id
  • grantee_principal_id - ID of the database principal for which the right is being granted
  • permission_name - such as SELECT, EXECUTE...
  • state_desc - permission state description
• sys.sysobjects - returns a row for each securable object in the SQL Server instance
  • id - id of the object
  • name - name of the database object
  • type - type of object.  For a full listing of object type codes please consult Microsoft Books Online.
  • uid - id of the schema owner for the object

1. With this information in hand, let's look at the object ownership and rights granted to the Public role in (firstly) the master database:

SELECT SDP.state_desc, SDP.permission_name, SSU.[name] AS "Schema" SSO.[name], SSO.[type] 
FROM sys.sysobjects SSO INNER JOIN sys.database_permissions SDP ON SSO.id = SDP.major_id  
   INNER JOIN sys.sysusers SSU ON SSO.uid = SSU.uid 
ORDER BY SSU.[name], SSO.[name]

2. 列出数据库主题的所有权限：

SELECT pr.principal_id
    ,pr.name
    ,pr.type_desc
    ,pr.authentication_type_desc
    ,pe.state_desc
    ,pe.permission_name  
FROM sys.database_principals AS pr  
INNER JOIN sys.database_permissions AS pe ON pe.grantee_principal_id = pr.principal_id;

3. 列出对数据库中架构对象的权限

SELECT pr.principal_id
    ,pr.name
    ,pr.type_desc
    ,pr.authentication_type_desc
    ,pe.state_desc
    ,pe.permission_name
    ,s.name + '.' + o.name AS ObjectName
FROM sys.database_principals AS pr
INNER JOIN sys.database_permissions AS pe ON pe.grantee_principal_id = pr.principal_id
INNER JOIN sys.objects AS o ON pe.major_id = o.object_id
INNER JOIN sys.schemas AS s ON o.schema_id = s.schema_id;

4. 查找分配给以具体某个数据库对象的权限，以此表为例 dbo.vAssocSeqOrders

SELECT pr.principal_id
    ,pr.name
    ,pr.type_desc
    ,pr.authentication_type_desc
    ,pe.state_desc
    ,pe.permission_name
    ,s.name + '.' + o.name AS ObjectName
FROM sys.database_principals AS pr
INNER JOIN sys.database_permissions AS pe ON pe.grantee_principal_id = pr.principal_id
INNER JOIN sys.objects AS o ON pe.major_id = o.object_id
INNER JOIN sys.schemas AS s ON o.schema_id = s.schema_id
WHERE o.name = 'vAssocSeqOrders'
    AND s.name = 'dbo';

参考文档：

sys.database_permissions (Transact-SQL) - SQL Server | Microsoft Learn