/****** Object: StoredProcedure [dbo].[sp_helprotect2]
by: wzard http://www.experts-exchange.com/Database/Miscellaneous/Q_21694613.html
Edited and Reposted by Lars Rasmussen on http://larsrasmussen.blogspot.com/
******/
SET NOCOUNT
ONGO
PRINT 'Using Master database'
USE master
GO
PRINT 'Checking if procedure already exists in master database...'
IF (SELECT OBJECT_ID('sp_helprotect2','P')) IS NOT NULL --means, the procedure already existsBEGINPRINT 'Procedure already exists - dropping it.'
DROP PROC sp_helprotect2
END GO
CREATE PROCEDURE [dbo]
.[sp_helprotect2]
@name ncharacter varying(776) = NULL
,@username sysname = NULL
,@grantorname sysname = NULL
,@permissionarea character varying(10) = 'o s'
as
BEGIN
/********
Explanation of the parms...
---------------------------
@name: Name of [Owner.]Object and Statement; meaning
for sysprotects.id and sysprotects.action at the
same time; thus see parm @permissionarea.
Examples- 'user2.tb' , 'CREATE TABLE', null
@username: Name of the grantee (for sysprotects.uid).
Examples- 'user2', null
@grantorname: Name of the grantor (for sysprotects.grantor).
Examples- 'user2' --Would prevent report rows which would -- have 'dbo' as grantor.
@permissionarea: O=Object, S=Statement; include all which apply.
Examples- 'o' , ',s' , 'os' , 'so' , 's o' , 's,o'
GeneMi
********/
Set nocount on
Declare
@vc1 sysname
,@Int1 integer
Declare
@charMaxLenOwner character varying(11)
,@charMaxLenObject character varying(11)
,@charMaxLenGrantee character varying(11)
,@charMaxLenGrantor character varying(11)
,@charMaxLenAction character varying(11)
,@charMaxLenColumnName character varying(11)
Declare
@OwnerName sysname
,@ObjectStatementName sysname
/* Perform temp table DDL here to minimize compilation costs*/
CREATE Table #t1_Prots
( Id
int Null,Type1Code char(6) collate database_default NOT Null
,ObjType char(2) collate database_default Null
,ActionName varchar(20) collate database_default Null
,ActionCategory char(2) collate database_default Null
,ProtectTypeName char(10) collate database_default Null
,Columns_Orig varbinary(32) Null
,OwnerName sysname collate database_default NOT Null
,ObjectName sysname collate database_default NOT Null
,GranteeName sysname collate database_default NOT Null
,GrantorName sysname collate database_default NOT Null
,ColumnName sysname collate database_default Null
,ColId smallint Null
,Max_ColId smallint Null
,All_Col_Bits_On tinyint Null
,new_Bit_On tinyint Null ) -- 1=yes on
/* Check for valid @permissionarea */
Select @permissionarea = upper( isnull(@permissionarea,'?') )
IF ( charindex('O',@permissionarea
) <= 0
AND charindex('S',@permissionarea) <= 0)
beginraiserror(15300,-1,-1 ,@permissionarea,'o,s')
return (1)
end select @vc1 = parsename(@name,3)
/* Verified db qualifier is current db*/
IF (@vc1
is not null and @vc1
<> db_name())
beginraiserror(15302,-1,-1) --Do not qualify with DB name.
return (1)
end /* Derive OwnerName and @ObjectStatementName*/
select @OwnerName
= parsename(@name
, 2
),@ObjectStatementName = parsename(@name, 1)
IF (@ObjectStatementName
is NULL and @name
is not null)
beginraiserror(15253,-1,-1,@name)
return (1)
end /* Copy info from sysprotects for processing */
IF charindex('O',@permissionarea
) > 0
begin/* Copy info for objects */
INSERT #t1_Prots
( Id
,Type1Code
,ObjType
,ActionName
,ActionCategory
,ProtectTypeName
,Columns_Orig
,OwnerName
,ObjectName
,GranteeName
,GrantorName
,ColumnName
,ColId
,Max_ColId
,All_Col_Bits_On
,new_Bit_On )
/* 1Regul indicates action can be at column level,2Simpl indicates action is at the object level */
SELECT id
,casewhen columns is null then '2Simpl'
else '1Regul'
end ,Null
,val1.name
,'Ob'
,val2.name
,columns
,user_name(objectproperty( id, 'ownerid' ))
,object_name(id)
,user_name(uid)
,user_name(grantor
)
,casewhen columns is null then '.'
else Null
end
,-123
,Null
,Null
,Null
FROM sysprotects sysp
,master.dbo.spt_values val1
,master.dbo.spt_values val2
where (@OwnerName
is null or user_name(objectproperty( id
, 'ownerid' )) = @OwnerName
)
and (@ObjectStatementName
is null or object_name(id
) = @ObjectStatementName
)
and (@username
is null or user_name(uid
) = @username
)
and (@grantorname
is null or user_name(grantor
) = @grantorname
)
and val1
.type
= 'T'
and val1
.number
= sysp
.action
and val2
.type
= 'T' --T is overloaded.
and val2
.number
= sysp
.protecttype
and sysp
.id
!= 0
IF EXISTS (SELECT * From #t1_Prots
)
beginUPDATE #t1_Prots set ObjType = ob.xtype
FROM sysobjects ob
WHERE ob.id = #t1_Prots.Id
UPDATE #t1_Prots
set Max_ColId
= (select max(colid
) from syscolumns sysc
where #t1_Prots.Id = sysc.id) -- colid may not consecutive if column dropped
where Type1Code
= '1Regul'
/* First bit set indicates actions pretains to new columns. (i.e. table-level permission)Set new_Bit_On accordinglly */
UPDATE #t1_Prots
SET new_Bit_On
=
CASE convert(int,substring(Columns_Orig
,1
,1
)) & 1
WHEN 1 then 1
ELSE 0
END
WHERE ObjType
<> 'V' and Type1Code
= '1Regul'
/* Views don't get new columns */
UPDATE #t1_Prots set new_Bit_On = 0
WHERE ObjType = 'V'
/* Indicate enties where column level action pretains to allcolumns in table All_Col_Bits_On = 1 */
UPDATE #t1_Prots
set All_Col_Bits_On
= 1
where #t1_Prots
.Type1Code
= '1Regul'
and not exists (select *
from syscolumns sysc, master..spt_values v
where #t1_Prots.Id = sysc.id and sysc.colid = v.number
and v.number <= Max_ColId -- column may be dropped/added after Max_ColId snap-shot
and v.type = 'P' and
/* Columns_Orig where first byte is 1 means off means on and on mean offwhere first byte is 0 means off means off and on mean on */case convert(int,substring(#t1_Prots
.Columns_Orig
, 1
, 1
)) & 1
when 0 then convert(tinyint, substring(#t1_Prots.Columns_Orig, v.low, 1))
else (~convert(tinyint, isnull(substring(#t1_Prots.Columns_Orig, v.low, 1),0)))
end & v
.high
= 0
)
/* Indicate entries where column level action pretains toonly some of columns in table All_Col_Bits_On = 0*/
UPDATE #t1_Prots
set All_Col_Bits_On
= 0
WHERE #t1_Prots
.Type1Code
= '1Regul'
and All_Col_Bits_On
is null
Update #t1_Prots
set ColumnName
=
casewhen All_Col_Bits_On = 1 and new_Bit_On = 1 then '(All+New)'
when All_Col_Bits_On = 1 and new_Bit_On = 0 then '(All)'
when All_Col_Bits_On = 0 and new_Bit_On = 1 then '(New)'
end
from #t1_Prots
where ObjType
IN ('S ' ,'U ', 'V ')
and Type1Code
= '1Regul'
and NOT (All_Col_Bits_On
= 0
and new_Bit_On
= 0
)
/* Expand and Insert individual column permission rows */
INSERT into #t1_Prots
(Id
,Type1Code
,ObjType
,ActionName
,ActionCategory
,ProtectTypeName
,OwnerName
,ObjectName
,GranteeName
,GrantorName
,ColumnName
,ColId )
SELECT prot1
.Id
,'1Regul'
,ObjType
,ActionName
,ActionCategory
,ProtectTypeName
,OwnerName
,ObjectName
,GranteeName
,GrantorName
,col_name ( prot1.Id ,val1.number )
,val1.number
from #t1_Prots prot1
,master.dbo.spt_values val1
,syscolumns sysc
where prot1
.ObjType
IN ('S ' ,'U ' ,'V ')and prot1
.All_Col_Bits_On
= 0
and prot1
.Id
= sysc
.id
and val1
.type
= 'P'
and val1
.number
= sysc
.colid
and
case convert(int,substring(prot1
.Columns_Orig
, 1
, 1
)) & 1
when 0 then convert(tinyint, substring(prot1.Columns_Orig, val1.low, 1))
else (~convert(tinyint, isnull(substring(prot1.Columns_Orig, val1.low, 1),0)))
end & val1
.high
<> 0
delete from #t1_Prots
where ObjType
IN ('S ' ,'U ' ,'V ')and All_Col_Bits_On = 0
and new_Bit_On = 0
end
end
/* Handle statement permissions here*/
IF (charindex('S',@permissionarea
) > 0
)
begin /* All statement permissions are 2Simpl */INSERT #t1_Prots
( Id
,Type1Code
,ObjType
,ActionName
,ActionCategory
,ProtectTypeName
,Columns_Orig
,OwnerName
,ObjectName
,GranteeName
,GrantorName
,ColumnName
,ColId
,Max_ColId
,All_Col_Bits_On
,new_Bit_On )
SELECT id
,'2Simpl'
,Null
,val1.name
,'St'
,val2.name
,columns
,'.'
,'.'
,user_name(sysp.uid)
,user_name(sysp.grantor)
,'.'
,-123
,Null
,Null
,Null
FROM sysprotects sysp
,master.dbo.spt_values val1
,master.dbo.spt_values val2
where (@username
is null or user_name(sysp
.uid
) = @username
)and (@grantorname is null or user_name(sysp.grantor) = @grantorname)
and val1.type = 'T'
and val1.number = sysp.action
and (@ObjectStatementName is null or val1.name = @ObjectStatementName)
and val2.number = sysp.protecttype
and val2.type = 'T'
and sysp.id = 0
end
IF NOT EXISTS (SELECT * From #t1_Prots
)
beginraiserror(15330,-1,-1)
return (1)
end
/* Calculate dynamic display col widths */
SELECT@charMaxLenOwner
=convert ( varchar, max(datalength(OwnerName)))
,@charMaxLenObject
=convert ( varchar, max(datalength(ObjectName)))
,@charMaxLenGrantee
=convert ( varchar, max(datalength(GranteeName)))
,@charMaxLenGrantor
=convert ( varchar, max(datalength(GrantorName)))
,@charMaxLenAction
=convert ( varchar, max(datalength(ActionName)))
,@charMaxLenColumnName
=convert ( varchar, max(datalength(ColumnName)))
from #t1_Prots
/* Output the report */
create table #mytmp
([owner] varchar
(255
), [object] varchar
(255
), grantee varchar
(255
), grantor varchar
(255
), protecttype varchar
(255
), [action] varchar
(255
), [column] varchar
(255
))
EXECUTE(
'Set nocount off
INSERT INTO #mytmp
SELECT ''Owner'' = substring (OwnerName ,1 ,' + @charMaxLenOwner
+ ')
,''Object'' = substring (ObjectName ,1 ,' + @charMaxLenObject + ')
,''Grantee'' = substring (GranteeName ,1 ,' + @charMaxLenGrantee + ')
,''Grantor'' = substring (GrantorName ,1 ,' + @charMaxLenGrantor + ')
,''ProtectType''= ProtectTypeName
,''Action'' = substring (ActionName ,1 ,' + @charMaxLenAction + ')
,''Column'' = substring (ColumnName ,1 ,' + @charMaxLenColumnName + ')
from #t1_Prots
order byActionCategory
,Owner ,Object
,Grantee ,Grantor
,ProtectType ,Action
,ColId --Multiple -123s ( <0 ) possible
Set nocount on'
)
select [protecttype]
+ ' ' + [action]
+ ' on [' + [object]
+ '] to [' + [grantee]
+ ']' from [#mytmp]
Return (0
) -- sp_helprotect2
END
GO
PRINT 'Procedure created.'GO
--Mark procedure as system object
EXEC sp_MS_marksystemobject sp_helprotect2
GO
