在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