如何獲得SQL SERVER2000數(shù)據(jù)庫指定對象的權(quán)限列表？

[日期：2005-07-08]

來源：CSDN 作者：

[字體：大中小]

前幾天看到有人問是否可以方便的獲得SQL SERVER2000指定對象的權(quán)限和指定USER的權(quán)
限。我寫了一個存儲過程，可以獲得用戶和角色的權(quán)限。請大家?guī)兔y試一下?？纯词?br>否還有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

本站僅提供存儲服務，所有內(nèi)容均由用戶發(fā)布，如發(fā)現(xiàn)有害或侵權(quán)內(nèi)容，請點擊舉報。

打開APP，閱讀全文并永久保存查看更多類似文章

http://blog.csdn.net/liuzhoulong/article/deta...

sql 處理數(shù)據(jù)字段為NULL 若不為空則顯示該值，若為空轉(zhuǎn)換成別的值。

Implementing Audit Trail using Entity Framework

国产一级a片免费看高清,亚洲熟女中文字幕在线视频,黄三级高清在线播放,免费黄色视频在线看

如何獲得SQL SERVER2000數(shù)據(jù)庫指定對象的權(quán)限列表？

如何獲得SQL SERVER2000數(shù)據(jù)庫指定對象的權(quán)限列表？