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