--查詢用戶擁有的服務器角色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_nameFROM	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_idWHERE   p1.[name] = 'rpt'        AND p2.[name] IS NOT NULLORDER BY p1.[name] ASC;--查詢用戶擁有的數據庫角色USE TestDBGOSELECT  p1.[name]       ,p1.[sid]       ,p1.default_schema_name       ,p2.[name] db_role_name       ,p2.type_descFROM    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_idWHERE   p1.[name] = 'rpt'        AND p2.[name] IS NOT NULLORDER 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_descFROM    sys.database_permissions AS a        INNER JOIN sys.database_principals AS b ON b.principal_id = a.grantee_principal_idWHERE   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_nameFROM    sys.database_principals AS p        INNER JOIN sys.schemas AS s ON s.principal_id = p.principal_idWHERE   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_dateFROM    sys.server_permissions AS a        INNER JOIN sys.server_principals AS b ON b.principal_id = a.grantee_principal_idWHERE   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_descFROM    sys.database_permissions AS a        INNER JOIN sys.database_principals AS b ON b.principal_id = a.grantee_principal_idWHERE   b.type_desc = 'SQL_USER'        AND b.[name] = 'rpt'ORDER BY a.state ASC       ,b.[name] ASC       ,a.class ASC       ,a.[permission_name] ASC;