sql语句server中怎么查询用户的角色和权限？

SQL Server中查询用户的对象权限和角色的方法 \x0d\x0a\x0d\x0a-- 查询用户的object权限\x0d\x0aexec sp_helprotect NULL, 'UserName'\x0d\x0a-- 查询用户拥有的role\x0d\x0aexec sp_helpuser 'UserName'\x0d\x0a-- 查询哪些用户拥有指定的系统role\x0d\x0aexec sp_helpsrvrolemember 'sysadmin'\x0d\x0a-- 可查询嵌套role\x0d\x0aWITH tree_roles as\x0d\x0a(\x0d\x0aSELECT role_principal_id, member_principal_id\x0d\x0aFROM sys.database_role_members\x0d\x0aWHERE member_principal_id = USER_ID('UserName')\x0d\x0aUNION ALL\x0d\x0aSELECT c.role_principal_id,c.member_principal_id\x0d\x0aFROM sys.database_role_members as c\x0d\x0ainner join tree_roles\x0d\x0aon tree_roles.member_principal_id = c.role_principal_id\x0d\x0a)\x0d\x0aSELECT distinct USER_NAME(role_principal_id) RoleName\x0d\x0aFROM tree_roles\x0d\x0a\x0d\x0a-- 其他权限相关基本表\x0d\x0aselect * from sysusers\x0d\x0aselect * from syspermissions\x0d\x0a\x0d\x0a-- Who has access to my SQL Server instance?\x0d\x0aSELECT\x0d\x0aname as UserName, type_desc as UserType, is_disabled as IsDisabled\x0d\x0aFROM sys.server_principals\x0d\x0awhere type_desc in('WINDOWS_LOGIN', 'SQL_LOGIN')\x0d\x0aorder by UserType, name, IsDisabled\x0d\x0a\x0d\x0a-- Who has access to my Databases?\x0d\x0aSELECT\x0d\x0adp.name as UserName, dp.type_desc as UserType, sp.name as LoginName, sp.type_desc as LoginType\x0d\x0aFROM sys.database_principals dp\x0d\x0aJOIN sys.server_principals sp ON dp.principal_id = sp.principal_id\x0d\x0aorder by UserType\x0d\x0a\x0d\x0aselect * from sys.database_principals\x0d\x0a\x0d\x0a-- Server Roles\x0d\x0aselect\x0d\x0ap.name as UserName, p.type_desc as UserType, pp.name as ServerRoleName, pp.type_desc as ServerRoleType\x0d\x0afrom sys.server_role_members roles\x0d\x0ajoin sys.server_principals p on roles.member_principal_id = p.principal_id\x0d\x0ajoin sys.server_principals pp on roles.role_principal_id = pp.principal_id\x0d\x0awhere pp.name in('sysadmin')\x0d\x0aorder by ServerRoleName, UserName\x0d\x0a\x0d\x0a-- Database Roles\x0d\x0aSELECT\x0d\x0ap.name as UserName, p.type_desc as UserType, pp.name as DBRoleName, pp.type_desc as DBRoleType, pp.is_fixed_role as IfFixedRole\x0d\x0aFROM sys.database_role_members roles\x0d\x0aJOIN sys.database_principals p ON roles.member_principal_id = p.principal_id\x0d\x0aJOIN sys.database_principals pp ON roles.role_principal_id = pp.principal_id\x0d\x0awhere pp.name in('db_owner', 'db_datawriter')\x0d\x0a\x0d\x0a-- What can these users do?\x0d\x0aSELECT\x0d\x0agrantor.name as GrantorName, dp.state_desc as StateDesc, dp.class_desc as ClassDesc, dp.permission_name as PermissionName ,\x0d\x0aOBJECT_NAME(major_id) as ObjectName, GranteeName = grantee.name\x0d\x0aFROM sys.database_permissions dp\x0d\x0aJOIN sys.database_principals grantee on dp.grantee_principal_id = grantee.principal_id\x0d\x0aJOIN sys.database_principals grantor on dp.grantor_principal_id = grantor.principal_id\x0d\x0awhere permission_name like '%UPDATE%'

用数据字典select*fromdba_users查看数据库里面所有用户，前提是你是有dba权限的帐号，如sys,systemselect*fromall_users查看你能管理的所有用户！select*fromuser_users查看当前用户信息！

一

确定角色的权限：

select

*

from

role_tab_privs

包含了授予角色的对象权限

select

*

from

role_role_privs

包含了授予另一角色的角色

select

*

from

role_sys_privs

包含了授予角色的系统权限

二

确定用户帐户所授予的权限：

select

*

from

DBA_tab_privs

直接授予用户帐户的对象权限

select

*

from

DBA_role_privs

授予用户帐户的角色

select

*

from

DBA_sys_privs

授予用户帐户的系统权限

三

查看当前用户权限及角色:

SQL>

select

*

from

session_privs

SQL>

select

*

from

user_role_privs

四

查询某一角色的具体权限:

例如查看RESOURCE具有那些权限

SQL>

SELECT

*

FROM

DBA_SYS_PRIVS

WHERE

GRANTEE='RESOURCE'

---