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