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