如何用命令查看登录名的相关权限
例如一个public权限的登录名loginA
我只给他a数据的的查询权限 如何用语句查出他的权限
结果这样：
登录名 授权数据库 数据库角色 授权对象 授权类型 查询数据 插入数据 更新数据 删除数据 执行
loginA a public 表a 授权 √ √
存储过程a 授权 √
[解决办法]
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
注意，如果这个库没有这个用户的话，执行会给你抛出一个错误
