sp_helpprotect2 - Version of sp_helpprotect stored proc that Returns Valid T-sql Permissions

One common task with SQL Server is viewing and modifying table permissions.

sp_helprotect can spit out some very helpful reports, but the returned SQL does not comprise valid T-SQL statements unless the version of sp_helprotect in the master database is modified a bit. This modified version is called sp_helprotect2, and marks itself as a system object. Thanks to wzard on Experts Exchange for this syntax.

/****** 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 ON
GO


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 exists

BEGIN

PRINT '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)

begin

raiserror(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())
begin

raiserror(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)
begin

raiserror(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

,case

when 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)
,case

when 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)
begin

UPDATE #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 all

columns 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 off

where 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 to

only 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 =
case

when 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)
begin

raiserror(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 by

ActionCategory
,Owner ,Object
,Grantee ,Grantor
,ProtectType ,Action
,ColId --Multiple -123s ( &lt0 ) 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