如何获得SQL SERVER2000数据库指定对象的权限列表？

前几天看到有人问是否可以方便的获得SQL SERVER2000指定对象的权限和指定USER的权
限。我写了一个存储过程，可以获得用户和角色的权限。请大家帮忙测试一下。看看是
否还有BUG:-)

IF OBJECTPROPERTY( OBJECT_ID( @#usp_getObjectAuthor@# ) , @#IsProcedure@# ) =1
        DROP PROC usp_getObjectAuthor
GO
/***************************************************************************
*****/
/* Created By : leimin                                   */
/* Created On : 29 May 2004                                              */
/* Description : This stored procedure returns the object permission which
you */
/*                GRANT,DENY and REVOKE.
*/
/***************************************************************************
*****/
Create proc usp_getObjectAuthor
 @objectname sysname  = null,
 @username sysname = null
as
set nocount on
begin
/***************************************************************************
*****/
/* defined the initilization variable       */
/***************************************************************************
*****/
Declare @rc int
Declare @rowcount int
Declare @groupid int

Set @rc=0
Set @rowcount=0

/***************************************************************************
*****/
/*  Judge the input parameters ,if @objectname is null  and @username is
null  */
/*  then return all objects authorization.     */
/***************************************************************************
*****/
if @objectname is null and @username is null
begin
  select  object_name(a.id) as objectname,
   user_name(a.uid) as usename,
   case b.issqlrole when 1 then @#Group @#
      else @#User@#
   end as Role,
   case a.protecttype when 205 then @#Grant@#
      when 204 then @#Grant@#
      when 206 then @#Deny@#
      else @#Revoke@#
   end as ProtectType,
   case a.[action] when 26  then @#REFERENCES@#
        when 178 then @#CREATE FUNCTION@#
        when 193 then @#SELECT@#
                      when 195 then @#INSERT@#
                      when 196 then @#DELETE@#
                      when 197 then @#UPDATE@#
        when 198 then @#CREATE TABLE@#
        when 203 then @#CREATE DATABASE@#
        when 207 then @#CREATE VIEW@#
                      when 222 then @#CREATE PROCEDURE@#
               when 224 then @#EXECUTE@#
                      when 228 then @#BACKUP DATABASE@#
               when 233 then @#CREATE DEFAULT@#
                      when 235 then @#BACKUP LOG@#
                      when 236 then @#CREATE RULE@#
                      else @#0@#
           end as [Action],
    user_name(a.grantor) as Grantor
  from sysprotects a inner join sysusers b on a.uid=b.uid
  where exists (select 1 from  sysobjects
         where [name]=object_name(a.id) and xtype <>@#S@# )
  order by object_name(a.id)

 select @rowcount=@@rowcount
 if @rowcount=0
  begin
   select @rc=-1
   print @#There a no user objects in database!@#
   return @rc
  end
end
/***************************************************************************
*****/
/*  Judge the input parameters ,if @objectname is null  and @username is not
null */
/*  then return all objects authorization where relation @username  */
/*  if the user belong to a group ,so we must add the group authorization */
/***************************************************************************
*****/
if  @rc=0 and @username is not null and @objectname is null
begin
 if not exists(select * from sysusers where [uid]=user_id(@username) and
status<>0)
 begin
  select @rc=-2
  print @#The user name is not include in sysusers table.@#
  return @rc
 end

 if exists(select 1 from sysmembers where [memberuid]=user_id(@username))
 begin
  select  object_name(a.id) as objectname,
   user_name(a.uid) as usename,
   case b.issqlrole when 1 then @#Group @#
      else @#User@#
   end as Role,
   case a.protecttype when 205 then @#Grant@#
      when 204 then @#Grant@#
      when 206 then @#Deny@#
      else @#Revoke@#
   end as ProtectType,
   case a.[action] when 26  then @#REFERENCES@#
        when 178 then @#CREATE FUNCTION@#
        when 193 then @#SELECT@#
                      when 195 then @#INSERT@#
                      when 196 then @#DELETE@#
                      when 197 then @#UPDATE@#
        when 198 then @#CREATE TABLE@#
        when 203 then @#CREATE DATABASE@#
        when 207 then @#CREATE VIEW@#
                      when 222 then @#CREATE PROCEDURE@#
               when 224 then @#EXECUTE@#
                      when 228 then @#BACKUP DATABASE@#
               when 233 then @#CREATE DEFAULT@#
                      when 235 then @#BACKUP LOG@#
                      when 236 then @#CREATE RULE@#
                      else @#0@#
           end as [Action],
    user_name(a.grantor) as Grantor
  from sysprotects a inner join sysusers b on a.uid=b.uid
  where exists (select 1 from  sysobjects
         where [name]=object_name(a.id) and xtype <>@#S@# )
  and ( exists (select 1 from sysmembers
    where groupuid=a.uid and memberuid=user_id(@username))
  or a.uid=user_id(@username))
  order by object_name(a.id)

  select @rowcount=@@rowcount
  if @rowcount=0
   begin
    select @rc=-3
    print @username+@# have not any objects authorization.@#
    return @rc
   end
 end
 else
 begin
  select  object_name(a.id) as objectname,
   user_name(a.uid) as usename,
   case b.issqlrole when 1 then @#Group @#
      else @#User@#
   end as Role,
   case a.protecttype when 205 then @#Grant@#
      when 204 then @#Grant@#
      when 206 then @#Deny@#
      else @#Revoke@#
   end as ProtectType,
   case a.[action] when 26  then @#REFERENCES@#
        when 178 then @#CREATE FUNCTION@#
        when 193 then @#SELECT@#
                      when 195 then @#INSERT@#
                      when 196 then @#DELETE@#
                      when 197 then @#UPDATE@#
        when 198 then @#CREATE TABLE@#
        when 203 then @#CREATE DATABASE@#
        when 207 then @#CREATE VIEW@#
                      when 222 then @#CREATE PROCEDURE@#
               when 224 then @#EXECUTE@#
                      when 228 then @#BACKUP DATABASE@#
               when 233 then @#CREATE DEFAULT@#
                      when 235 then @#BACKUP LOG@#
                      when 236 then @#CREATE RULE@#
                      else @#0@#
           end as [Action],
    user_name(a.grantor) as Grantor
  from sysprotects a inner join sysusers b on a.uid=b.uid
  where exists (select 1 from  sysobjects
         where [name]=object_name(a.id) and xtype <>@#S@# )
  and  a.uid=user_id(@username)
  order by object_name(a.id)

  select @rowcount=@@rowcount
  if @rowcount=0
   begin
    select @rc=-4
    print @username+@# have not any objects authorization.@#
    return @rc
   end
 end

end
/***************************************************************************
*****/
/*  Judge the input parameters ,if @objectname is not null  and @username is
null */
/*  then return one objects authorization     */
/***************************************************************************
*****/
if @rc=0 and @objectname is not null and @username is null
begin
 if not exists(select * from sysobjects where [id]=object_id(@objectname)
and xtype<>@#S@#)
 begin
  select @rc=-5
  return @rc
 end
 if @rc=0
  begin
  select  object_name(a.id) as objectname,
   user_name(a.uid) as usename,
   case b.issqlrole when 1 then @#Group @#
      else @#User@#
   end as Role,
   case a.protecttype when 205 then @#Grant@#
      when 204 then @#Grant@#
      when 206 then @#Deny@#
      else @#Revoke@#
   end as ProtectType,
   case a.[action] when 26  then @#REFERENCES@#
        when 178 then @#CREATE FUNCTION@#
        when 193 then @#SELECT@#
                      when 195 then @#INSERT@#
                      when 196 then @#DELETE@#
                      when 197 then @#UPDATE@#
        when 198 then @#CREATE TABLE@#
        when 203 then @#CREATE DATABASE@#
        when 207 then @#CREATE VIEW@#
                      when 222 then @#CREATE PROCEDURE@#
               when 224 then @#EXECUTE@#
                      when 228 then @#BACKUP DATABASE@#
               when 233 then @#CREATE DEFAULT@#
                      when 235 then @#BACKUP LOG@#
                      when 236 then @#CREATE RULE@#
                      else @#0@#
           end as [Action],
    user_name(a.grantor) as Grantor
  from sysprotects a inner join sysusers b on a.uid=b.uid
  where exists (select 1 from  sysobjects
         where [name]=object_name(a.id) and xtype <>@#S@# )
  and [id]=object_id(@objectname)
  order by object_name(a.id)

  select @rowcount=@@rowcount
  if @rowcount=0
   begin
    select @rc=-6
    print @objectname+@# have not grant authorization to any user@#
    return @rc
   end
  end
end
/***************************************************************************
*****/
/*  Judge the input parameters ,if @objectname is not null  and @username is
not null */
/*  then return one objects authorization by one user    */
/***************************************************************************
*****/
if @rc=0 and @objectname is not null and @username is not null
begin
 if not exists(select * from sysobjects where [id]=object_id(@objectname)
and xtype<>@#S@#)
 begin
  select @rc=-7
  print @#The object name  is not include in sysobjects table.@#
  return @rc
 end

 if not exists(select * from sysusers where [uid]=user_id(@username) and
status<>0)
 begin
  select @rc=-8
  print @#The user name is not include in sysusers table.@#
  return @rc
 end

 if exists(select 1 from sysmembers where [memberuid]=user_id(@username))
 begin
  select  object_name(a.id) as objectname,
   user_name(a.uid) as usename,
   case b.issqlrole when 1 then @#Group @#
      else @#User@#
   end as Role,
   case a.protecttype when 205 then @#Grant@#
      when 204 then @#Grant@#
      when 206 then @#Deny@#
      else @#Revoke@#
   end as ProtectType,
   case a.[action] when 26  then @#REFERENCES@#
        when 178 then @#CREATE FUNCTION@#
        when 193 then @#SELECT@#
                      when 195 then @#INSERT@#
                      when 196 then @#DELETE@#
                      when 197 then @#UPDATE@#
        when 198 then @#CREATE TABLE@#
        when 203 then @#CREATE DATABASE@#
        when 207 then @#CREATE VIEW@#
                      when 222 then @#CREATE PROCEDURE@#
               when 224 then @#EXECUTE@#
                      when 228 then @#BACKUP DATABASE@#
               when 233 then @#CREATE DEFAULT@#
                      when 235 then @#BACKUP LOG@#
                      when 236 then @#CREATE RULE@#
                      else @#0@#
           end as [Action],
    user_name(a.grantor) as Grantor
  from sysprotects a inner join sysusers b on a.uid=b.uid
  where exists (select 1 from  sysobjects
         where [name]=object_name(a.[id]) and xtype <>@#S@# )
  and (exists (select 1 from sysmembers
    where groupuid=a.uid and memberuid=user_id(@username))
  or a.uid=user_id(@username))
  and [id]=object_id(@objectname)
  order by object_name(a.id)

  select @rowcount=@@rowcount
  if @rowcount=0
   begin
    select @rc=-9
    print @username+@# have not any objects authorization.@#
    return @rc
   end
 end
 else
 begin
  select  object_name(a.id) as objectname,
   user_name(a.uid) as usename,
   case b.issqlrole when 1 then @#Group @#
      else @#User@#
   end as Role,
   case a.protecttype when 205 then @#Grant@#
      when 204 then @#Grant@#
      when 206 then @#Deny@#
      else @#Revoke@#
   end as ProtectType,
   case a.[action] when 26  then @#REFERENCES@#
        when 178 then @#CREATE FUNCTION@#
        when 193 then @#SELECT@#
                      when 195 then @#INSERT@#
                      when 196 then @#DELETE@#
                      when 197 then @#UPDATE@#
        when 198 then @#CREATE TABLE@#
        when 203 then @#CREATE DATABASE@#
        when 207 then @#CREATE VIEW@#
                      when 222 then @#CREATE PROCEDURE@#
               when 224 then @#EXECUTE@#
                      when 228 then @#BACKUP DATABASE@#
               when 233 then @#CREATE DEFAULT@#
                      when 235 then @#BACKUP LOG@#
                      when 236 then @#CREATE RULE@#
                      else @#0@#
           end as [Action],
    user_name(a.grantor) as Grantor
  from sysprotects a inner join sysusers b on a.uid=b.uid
  where exists (select 1 from  sysobjects
         where [name]=object_name(a.[id]) and xtype <>@#S@# )
  and  a.uid=user_id(@username)
  and  [id]=object_id(@objectname)
  order by object_name(a.id)

  select @rowcount=@@rowcount
  if @rowcount=0
   begin
    select @rc=-10
    print @username+@# have not any objects authorization.@#
    return @rc
   end
 end
   end
end
go
exec usp_getObjectAuthor

原文转自：http://www.ltesting.net