Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_helprotect

  No additional text.


Syntax
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 database_default NOT Null
		,ObjType				char(2)		collate database_default Null

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

		,ColId				int			Null

		,OwnerName			sysname		collate database_default NOT Null
		,ObjectName			sysname		collate database_default NOT Null

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

		,ColumnName			sysname		collate database_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 database_default
				,sysp.type collate database_default
				,sysp.class
				,sysp.state collate database_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 database_default
					,p.type collate database_default
					,p.class
					,p.state collate database_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 database_default
				,class
				,state collate database_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 database_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

 
Last revision 2008RTM
News

  Query a named instance
Sybase+ASE+silent+install
SQL 2008 R2
Deprecated procedures in SQL2008
Reporting Services item-level role definitions
Create all your missing indexes
Converting datetime field
Start MSSQL Server Profiler at time
Replicating MSSQL Server views
Exploring Microsoft Sharepoint
The OLE DB provider "SQLNCLI10" for linked server indicates.
Mobile solar charger
Oracle to SQL Server replication
Cannot insert the value NULL into column
Undocumented Microsoft SQL Server 2008
VMware
Zoekmachine optimalisatie
SQL Servers hidden objects
FckEditor
Flash in Ajax
De hype die AJAX heet
Barcode scannen
MySQL; Gratis, makkelijk en snel!
PHP
sIFR; de combinatie tussen HTML en Flash