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'''' &nbs
注：本站部分文章源于互联网，版权归原作者所有！如有侵权，请原作者与本站联系，本站将立即删除！ 本站文章除特别注明外均可转载，但需注明出处！ [MinTao学以致用网] 
