登录名、用户、角色、权限

--查询用户拥有的服务器角色
SELECT	p1.[name] AS login_name
       ,p1.[sid]
       ,p2.[name] server_role_name
       ,p2.type_desc
       ,p1.is_disabled
       ,p1.create_date
	   ,p1.modify_date
       ,p1.default_database_name
       ,p1.default_language_name
FROM	sys.server_principals AS p1
		LEFT JOIN sys.server_role_members AS m ON m.member_principal_id = p1.principal_id
		LEFT JOIN sys.server_principals AS p2 ON p2.principal_id = m.role_principal_id
WHERE   p1.[name] = 'rpt'
        AND p2.[name] IS NOT NULL
ORDER BY p1.[name] ASC;



--查询用户拥有的数据库角色
USE TestDB
GO

SELECT  p1.[name]
       ,p1.[sid]
       ,p1.default_schema_name
       ,p2.[name] db_role_name
       ,p2.type_desc
FROM    sys.database_principals AS p1
        LEFT JOIN sys.database_role_members AS m ON m.member_principal_id = p1.principal_id
        LEFT JOIN sys.database_principals AS p2 ON p2.principal_id = m.role_principal_id
WHERE   p1.[name] = 'rpt'
        AND p2.[name] IS NOT NULL
ORDER BY p1.[name] ASC;



--查询数据库角色拥有的权限
SELECT  b.[name]
       ,b.sid
       ,b.type_desc AS principal_type_desc
       ,b.default_schema_name
       ,OBJECT_NAME(a.major_id) AS [object_name]
       ,a.class_desc
       ,a.[type] permission_type
       ,a.[permission_name]
       ,a.[state]
       ,a.state_desc
FROM    sys.database_permissions AS a
        INNER JOIN sys.database_principals AS b ON b.principal_id = a.grantee_principal_id
WHERE   b.type_desc = 'DATABASE_ROLE'
        AND b.[name] = 'udf_db_role_report'
ORDER BY a.state ASC
       ,b.[name] ASC
       ,a.class ASC
       ,a.[permission_name] ASC;
        


--查询用户拥有的架构
SELECT  p.[name]
       ,p.[sid]
       ,p.type_desc
       ,p.default_schema_name
       ,s.[name] AS own_schema_name
FROM    sys.database_principals AS p
        INNER JOIN sys.schemas AS s ON s.principal_id = p.principal_id
WHERE   p.[name] = 'rpt';



--查询用户的拥有的显式权限

--登录名在服务级别的权限
SELECT  b.name
       ,b.sid
       ,b.is_disabled
       ,b.type_desc AS principal_type_desc
       ,b.default_database_name
       ,a.class_desc
       ,a.[type] AS permission_type
       ,a.permission_name
       ,a.[state]
       ,a.state_desc
       ,b.create_date
       ,b.modify_date
FROM    sys.server_permissions AS a
        INNER JOIN sys.server_principals AS b ON b.principal_id = a.grantee_principal_id
WHERE   b.type_desc = 'SQL_LOGIN'
        AND b.[name] = 'rpt'
ORDER BY b.[name] ASC
       ,a.[state] ASC
       ,a.class ASC
       ,a.permission_name ASC;
       
--登录名在数据库级别的权限
--注意：用户至少要有数据库级别的CONNECT权限才能连接到指定的数据库
SELECT  b.[name]
       ,b.sid
       ,b.type_desc AS principal_type_desc
       ,b.default_schema_name
       ,OBJECT_NAME(a.major_id) AS [object_name]
       ,a.class_desc
       ,a.[type] permission_type
       ,a.[permission_name]
       ,a.[state]
       ,a.state_desc
FROM    sys.database_permissions AS a
        INNER JOIN sys.database_principals AS b ON b.principal_id = a.grantee_principal_id
WHERE   b.type_desc = 'SQL_USER'
        AND b.[name] = 'rpt'
ORDER BY a.state ASC
       ,b.[name] ASC
       ,a.class ASC
       ,a.[permission_name] ASC;