Tuesday, October 28, 2008

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

No comments: