查找没有授予给其它用户权限的表以及权限
以将dbo创建的所有表授予相应权限给普通用户userA为例。
将dbo拥有的表的相应权限（select/insert/update/delete）授予给普通用户userA，使用以下SQL生成授权语句：
select 'grant select on ' + user_name(uid) + '.' + name + ' to userA' from sysobjects where type='U' and uid=user_id('dbo')
union all
select 'grant insert on ' + user_name(uid) + '.' + name + ' to userA' from sysobjects where type='U' and uid=user_id('dbo')
union all
select 'grant update on ' + user_name(uid) + '.' + name + ' to userA' from sysobjects where type='U' and uid=user_id('dbo')
union all
select 'grant delete on ' + user_name(uid) + '.' + name + ' to userA' from sysobjects where type='U' and uid=user_id('dbo')
查看某张表的上用户的权限情况：
sp_helprotect table_name
查看用户所拥有的对象权限情况：
sp_helprotect user_name
如何知道哪些表的权限没有授予给userA，如何知道某张表的哪个权限没有授予给userA呢？
select 'grant ' + (case o.action
when 193 then 'select'
when 195 then 'insert'
when 196 then 'delete'
when 197 then 'update'
else 'all'
end)
+ ' on ' + user_name(o.uid) + '.' + o.name + ' to userA'
from
(select name,id,uid,type,action
from sysobjects,(select 193 'action' union select 195 union select 196 union select 197) p
) o
left join sysprotects p1 on o.id=p1.id and p1.uid=user_id('userA') and o.action=p1.action
where o.type='U' and o.uid=user_id('dbo') and p1.action is null
and o.name like '%%'
go