Sybase数据库技术，数据库恢复专家

以将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