April 30, 2012

sp_helprotect (Transact-SQL MetaData) Definition

Please note: that the following source code is provided and copyrighted by Microsoft and is for educational purpose only.
The meta data is from an SQL 2012 Server.

I have posted alot more, find the whole list here.

Goto Definition or MetaData

Definition:

sys.sp_helprotect(nvarchar @name
, nvarchar @username
, nvarchar @grantorname
, varchar @permissionarea)

MetaData:

 create procedure sys.sp_helprotect  
@name ncharacter varying(776) = NULL
,@username sysname = NULL
,@grantorname sysname = NULL
,@permissionarea character varying(10) = 'o s'
as

-- -- -- -- *
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
,@schid integer
,@grantor integer
,@grantee integer

-- Perform temp table DDL here to minimize compilation costs --
CREATE TABLE #t1_Prots(
Id int Null
,Type1Code char(6) collate catalog_default NOT Null
,ObjType char(2) collate catalog_default Null

,ActionName varchar(4) collate Latin1_General_CI_AS_KS_WS NOT NULL
,ActionFullName nvarchar(60) collate catalog_default Null
,ActionCategory tinyint not null
,ProtectTypeName char(10) collate catalog_default Null

,ColId int Null

,OwnerName sysname collate catalog_default NOT Null
,ObjectName sysname collate catalog_default NOT Null

,GranteeId int NOT Null
,GrantorId int NOT Null
,GranteeName sysname collate catalog_default NOT Null
,GrantorName sysname collate catalog_default NOT Null

,ColumnName sysname collate catalog_default Null
)


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

if (@OwnerName is not null)
begin
select @schid = schema_id(@OwnerName)
-- assign void schema id if not valid
if (@schid is null)
select @schid = 0
end

if (@username is not null)
begin
select @grantee = database_principal_id(@username)
if (@grantee is null)
select @grantee = -1 -- assign void user id
end

if (@grantorname is not null)
begin
select @grantor = database_principal_id(@grantorname)
if (@grantor is null)
select @grantor = -1 -- assign void user id
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

,ColId
,OwnerName
,ObjectName
,GranteeId
,GrantorId
,GranteeName
,GrantorName
,ColumnName)

-- 1Regul indicates action can be at column level,
2Simpl indicates action is at the object level --
select sysp.major_id
,case when sysp.type in ('RF','SL','UP') then '1Regul'
else '2Simpl'
end

,obj.type collate catalog_default
,sysp.type collate catalog_default
,sysp.class
,sysp.state collate catalog_default
,sysp.minor_id
,schema_name(obj.schema_id)
,obj.name
,sysp.grantee_principal_id
,sysp.grantor_principal_id
,user_name(sysp.grantee_principal_id)

,user_name(sysp.grantor_principal_id)
,'.'
from sys.database_permissions sysp
join sys.all_objects obj on obj.object_id = sysp.major_id
where sysp.class = 1
and (@schid is null or obj.schema_id = @schid)
and (@ObjectStatementName is null or obj.name = @ObjectStatementName)
and (@grantee is null or sysp.grantee_principal_id = @grantee)
and (@grantor is null or sysp.grantor_principal_id = @grantor)

IF EXISTS (select * from #t1_Prots)
begin

-- Indicate entries where column level action pertains to all columns in table
update #t1_Prots set ColumnName = '(All)'
where #t1_Prots.ColId = 0 and #t1_Prots.Type1Code = '1Regul'
and not exists
(select * from #t1_Prots col
where col.Id = #t1_Prots.Id and col.ColId > 0 and
col.GranteeId = #t1_Prots.GranteeId and col.GrantorId = #t1_Prots.GrantorId and
col.ActionName = #t1_Prots.ActionName)

-- Indicates actions pertain to new columns. (i.e. table-level permission)
-- Views and functions don't get new columns
update #t1_Prots set ColumnName = case ColumnName when '(All)' then '(All+New)' else '(New)' end
where ColId = 0 and ObjType = 'U' and Type1Code = '1Regul'

-- Indicate entries where column level action pertains to only some of columns in table
update #t1_Prots set ColumnName = col_name(#t1_Prots.Id, #t1_Prots.ColId)
where #t1_Prots.Type1Code = '1Regul' and #t1_Prots.ColId > 0

-- Propagate object-level SL/UP/RF permission to columns as appropriate
insert #t1_Prots (
Id
,Type1Code

,ObjType
,ActionName
,ActionCategory
,ProtectTypeName

,ColId
,OwnerName
,ObjectName
,GranteeId
,GrantorId
,GranteeName
,GrantorName
,ColumnName)
select c.object_id
,'1Regul'
,o.type collate catalog_default
,p.type collate catalog_default
,p.class
,p.state collate catalog_default
,c.column_id
,schema_name(o.schema_id)
,o.name
,p.grantee_principal_id
,p.grantor_principal_id
,user_name(p.grantee_principal_id)
,user_name(p.grantor_principal_id)
,c.name
from sys.columns c join sys.database_permissions p on p.class = 1 and p.major_id = c.object_id and p.minor_id = 0 and p.type in ('RF','SL','UP')
join sys.all_objects o on o.object_id = c.object_id
where not exists
(select * from sys.database_permissions m
where m.class = 1 and m.major_id = p.major_id and m.minor_id = c.column_id
and m.grantee_principal_id = p.grantee_principal_id and m.grantor_principal_id = p.grantor_principal_id
and m.type = p.type)
and exists
(select * from sys.database_permissions n
where n.class = 1 and n.major_id = p.major_id and n.minor_id > 0
and n.grantee_principal_id = p.grantee_principal_id and n.grantor_principal_id = p.grantor_principal_id
and n.type = p.type)

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
,ColId
,OwnerName

,ObjectName
,GranteeId
,GrantorId
,GranteeName
,GrantorName
,ColumnName )
select 0
,'2Simpl'
,Null
,type collate catalog_default
,class
,state collate catalog_default
,-123
,'.'
,'.'
,grantee_principal_id
,grantor_principal_id
,user_name(grantee_principal_id)
,user_name(grantor_principal_id)
,'.'
from sys.database_permissions
where class = 0 and major_id = 0
and (@grantee is null or grantee_principal_id = @grantee)
and (@grantor is null or grantor_principal_id = @grantor)
and (@ObjectStatementName is null or permission_name = @ObjectStatementName)

end


IF NOT EXISTS (select * from #t1_Prots)
begin
raiserror(15330,-1,-1)
return (1)
end

-- Don't show any Revoke rows (Shiloh compat)
DELETE #t1_Prots WHERE ProtectTypeName = 'R'

-- Translate ProtectTypeName and ActionFullName to full name, using Shiloh values when possible
UPDATE #t1_Prots
SET ProtectTypeName = CASE ProtectTypeName
WHEN 'G' THEN 'Grant' WHEN 'D' THEN 'Deny' WHEN 'W' THEN 'Grant_WGO' END
, ActionFullName = ISNULL(
(SELECT v.name FROM sys.syspalnames v WHERE v.class = 'HPRT' AND v.value = ActionName),
permission_name(ActionCategory, ActionName) )

-- 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(ActionFullName)))

,@charMaxLenColumnName =
convert ( varchar, max(datalength(ColumnName)))
from #t1_Prots

-- Output the report --
EXECUTE(
'set nocount off

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 (ActionFullName collate catalog_default,1 ,' + @charMaxLenAction + ')

,'
'Column'' = substring (ColumnName ,1 ,' + @charMaxLenColumnName + ')
from #t1_Prots
order by
ActionCategory desc
,Owner ,Object
,Grantee ,Grantor
,ProtectType ,Action
,ColId -- Multiple -123s ( <0 ) possible

set nocount on'

)

return (0) -- sp_helprotect

No comments:

Post a Comment

Total Pageviews