SQL SERVER 查看用户权限

最新推荐文章于 2024-04-08 10:36:03 发布

ynigeng

最新推荐文章于 2024-04-08 10:36:03 发布

阅读量1.1w

点赞数

分类专栏： SQL SERVER 文章标签： sql server join insert delete action object

本文链接：https://blog.youkuaiyun.com/ynigeng/article/details/5076994

版权

SQL SERVER 专栏收录该内容

41 篇文章

订阅专栏

本文提供了一组SQL查询语句，用于获取系统中用户的详细权限信息，包括用户与角色的关系、用户或角色在数据库中的授权详情及组织授权情况。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

--用户与角色关系
select a.uid as uid,a.status as uStatus,a.name as uName,
b.uid as rId,b.status as rStatus,b.name as rName
from sysusers a left join sysmembers m on m.memberuid = a.uid
left join sysusers b on b.gid = m.groupuid
where a.issqluser =1
--a.islogin =1
--b.issqlrole

--用户或角色在数据库中的授权信息

select b.id,b.name as tName,b.type,a.uid,c.name as uName,a.action,a.protecttype,a.columns
from sysprotects a inner join sysobjects b on a.id = b.id
inner join sysusers c on a.uid = c.uid

WHERE A.UID > 0

或则

select OBJECT_NAME(A.id) as tName,a.uid,c.name as uName,a.action,a.protecttype,a.columns
from sysprotects a inner join sysusers c on a.uid = c.uid
WHERE A.UID > 0

或则

select b.name as tName,c.name as objname,
CASE b.type
WHEN 'U' THEN 'Table'
WHEN 'P' THEN 'SP'
ELSE 'OTHER'
END AS TYPE,
CASE WHEN a.ACTION = 26 AND a.PROTECTTYPE = 205 THEN '√' ELSE '' END AS 'REFERENCES',
CASE WHEN a.ACTION = 193 AND a.PROTECTTYPE = 205 THEN '√' ELSE '' END AS 'SELECT',
CASE WHEN a.ACTION = 195 AND a.PROTECTTYPE = 205 THEN '√' ELSE '' END AS 'INSERT',
CASE WHEN a.ACTION = 197 AND a.PROTECTTYPE = 205 THEN '√' ELSE '' END AS 'UPDATE',
CASE WHEN a.ACTION = 196 AND a.PROTECTTYPE = 205 THEN '√' ELSE '' END AS 'DELETE',
CASE WHEN a.ACTION = 224 AND a.PROTECTTYPE = 205 THEN '√' ELSE '' END AS 'EXECUTE',
CASE a.PROTECTTYPE
WHEN 204 THEN 'GRANT_W_GRANT '
WHEN 205 THEN 'GRANT'
WHEN 206 THEN 'DENY'
ELSE 'OTHER' --当有other出现的时候，需要将其他的PROTECTTYPE添加进去。
END AS PROTECTTYPE
from sysprotects a inner join sysobjects b on a.id = b.id
inner join sysusers c on a.uid = c.uid
WHERE c.name in ('M18Console')
order by tname

--组织授权SQL

select
'GRANT '+ CASE a.ACTION
     WHEN 26 THEN 'REFERENCES'
     WHEN 193 THEN 'SELECT'
     WHEN 195 THEN 'INSERT'
     WHEN 196 THEN 'DELETE'
     WHEN 197 THEN 'UPDATE'
     WHEN 224 THEN 'EXECUTE'
    ELSE 'OTHER' --当有other出现的时候，需要将其他的ACTION类型添加进去。
END +' ON '+ b.name +' TO '+ c.name
from sysprotects a(nolock) inner join sysobjects b(nolock) on a.id = b.id
  inner join sysusers c (nolock)on a.uid = c.uid
WHERE A.UID > 0 and c.name = 'm18style'