在SQL Server中如何獲取某用戶對某個表中所有權限

在做權限管理的時候，必須檢索某個用戶對某個表的權限，而用戶是屬于某個角色的，用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