在做权限管理的时候，必须检索某个用户对某个表的权限，而用户是属于某个角色的，用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
