|
在做权限管理的时候，必须检索某个用户对某个表的权限，而用户是属于某个角色的，用SQL Server的sp_helprotect只能得到给某用户显式授予的权限，而检索不出继承的权限，下面给出一个过程，能够检索某用户所有的权限，包括继承来的权限 调用实例：
exec getTablePrivileges @ObjectName=''''custorder'''',@User=''''yahong'''' 过程主体：
alter procedure getTablePrivileges @ObjectName sysname=null,@User sysname
as
begin
-- declare @User sysname
-- set @User=''''saler''''
-- set @User=''''orderman'''' declare @curUser sysname,@Level int create table #temp
(
Owner sysname,
TableName sysname,
UserName sysname,
Grantor sysname,
ProtectType varchar(20),
Privilege varchar(20),
ColumnName varchar(20)
) create table #Privilege
(
TableName sysname,
UserName sysname,
ProtectType varchar(20),
Privilege varchar(20),
ColumnName sysname,
Level int
) create table #UserLevel
(
UserName sysname,
Level int
) declare cur_usertree cursor for
select UserName,Level from getUserTree(@User,1)
order by Level desc open cur_usertree
fetch next from cur_usertree into @curUser,@Level while @@fetch_status=0
begin
insert into #temp
exec sp_helprotect @name=@ObjectName,@UserName=@curUser insert into #UserLevel values(@curUser,@Level)
fetch next from cur_usertree into @curUser,@Level
end close cur_usertree
DEALLOCATE cur_usertree
insert into #Privilege
select TableName,
UserName,
ProtectType,
Privilege,
ColumnName,
(select Level from #UserLevel where UserName=O.UserName) Level
from #temp O
where ColumnName<>''''(ALL+New)''''
and ColumnName<>''''(ALL)''''
and ColumnName<>''''(New)''''
and (Privilege=''''SELECT'''' or Privilege=''''UPDATE'''')
and ProtectType<>''''Deny''''
insert into #Privilege
select a.TableName,a.UserName,a.ProtectType,a.Privilege,b.name,
(select Level from #UserLevel where UserName=a.UserName) Level
from #temp a join syscolumns b
on object_id(TableName)=b.id
where (a. ColumnName=''''(ALL+New)'''' or ColumnName=''''(ALL)'''' )
and a.ProtectType<>''''Deny'''' select * from #Privilege
drop table #Privilege
drop table #temp
drop table #UserLevel
end
| 
注：本站部分文章源于互联网，版权归原作者所有！如有侵权，请原作者与本站联系，本站将立即删除！ 本站文章除特别注明外均可转载，但需注明出处！ [MinTao学以致用网] 