在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

Copyright ＠ 2007-2012 敏韬网(敏而好学，文韬武略--MinTao.Net)（学习笔记） Inc All Rights Reserved.
闵涛 E_mail:admin@mintao.net(欢迎提供学习资源)

鄂公网安备 42011102001154号

站长：MinTao ICP备案号：鄂ICP备11006601号-18