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

--查询用户拥有的服务器角色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;