SQL Server：查看数据库用户权限（SQL 2005）

泰山神

　　1. 查看 SQL 2005 用户所属数据库角色

use yourdb
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
2. 查看 SQL 2005 登陆帐户所属服务器角色
use master
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
3. 查看 SQL 2005 用户被赋予的权限
use yourdb
go
exec sp_helprotect @username = 'user name'
go

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