SQL Server查询角色/用户

sdm_seven

已于 2023-04-28 01:49:05 修改

阅读量6.8k

点赞数

分类专栏： SQL Server 文章标签：服务器数据库大数据

于 2022-07-15 11:10:56 首次发布

原文链接：https://www.csdn.net/tags/MtzaAg4sOTExMzAtYmxvZwO0O0OO0O0O.html

版权

SQL Server 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

查询用户角色：

select UserName = u.name,DbRole = g.name
from sys.database_principals u, sys.database_principals g, sys.database_role_members m 
where g.principal_id = m.role_principal_id 
and u.principal_id = m.member_principal_id 
order by 1, 2 
go

select DbRole = g.name, MemberName = u.name, MemberSID = u.sid  
from sys.database_principals u, sys.database_principals g, sys.database_role_members m  
where g.principal_id = m.role_principal_id  
and u.principal_id = m.member_principal_id  
order by 1, 2  
go

select SrvRole = g.name, MemberName = u.name, MemberSID = u.sid  
from sys.server_principals u, sys.server_principals g, sys.server_role_members m  
where g.principal_id = m.role_principal_id  
and u.principal_id = m.member_principal_id  
order by 1, 2  
go

看服务器角色：

select 用户名 = u.name,管理员权限 = g.name,是否在用 = u.is_disabled,MemberSID = u.sid  
from sys.server_principals u, sys.server_principals g, sys.server_role_members m  
where g.principal_id = m.role_principal_id  
and u.principal_id = m.member_principal_id  
and g.name = 'sysadmin'
order by 1, 2
go