作者：未知 来源：月光软件站 加入时间：2005-6-5　月光软件站

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

相关软件：