SQL SERVER 查看用户权限

绝地灬酷狼 2022-08-06 00:11 391阅读 0赞

\--用户与角色关系  
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'