Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_MSrepl_check_job_access

  No additional text.


Syntax

-- Name:
--		sp_MSrepl_check_job_access

-- Description:
--		This procedure should be used to validate wether the current
--		user in the current database has permission to access the
--		job identified by the job @id/@name and @step_id/@step_uid.

-- Parameters:
--		See definition

-- Returns:
--		0 - succeeded
--      1 - failed

-- Result:
--		None

-- Security:
--		Procedure is NOT PUBLIC so there are no security checks.

-- Requires Certificate signature for catalog access

CREATE PROCEDURE sys.sp_MSrepl_check_job_access
(
	@id 			uniqueidentifier	= NULL,
	@name			sysname				= NULL,
	@step_id		int					= NULL,
	@step_uid		uniqueidentifier	= NULL,
	@err_not_found	bit					= 1,
	@job_found		bit					= 0 OUTPUT
)
AS
BEGIN
	DECLARE @category_id	int,
			@subsystem		sysname,
			@database		sysname,
			@owner_sid		varbinary(85)
	
	SELECT @category_id 	= NULL,
			@subsystem 		= NULL,
			@database 		= NULL,
			@job_found		= 0

	-- Retrieve the JOB Category and Owner ID
	SELECT @owner_sid = owner_sid,
			@category_id = category_id
		FROM msdb.dbo.sysjobs
		WHERE job_id = @id
			OR name = @name

	-- Verify that the job was found
	IF @@ROWCOUNT < 1
	BEGIN
		IF @err_not_found = 1
		BEGIN
			DECLARE @str_id sysname

			SELECT @str_id = 'id = ' + CONVERT(nvarchar(36), @id)
				
			-- The specified job ('id = 4905f53c-903f-4e41-ad22-7f4c2982d9a1') does not exist.
			RAISERROR(14262, 16, -1, 'job', @str_id)
			RETURN 1
		END
		ELSE
		BEGIN
			-- exit without error
			RETURN 0
		END
	END

	SELECT @job_found = 1
		
	-- Retrieve the JOB Subsystem and Database
	SELECT @subsystem = sjs.subsystem,
				@database = sjs.database_name
			FROM msdb.dbo.sysjobs as sj
				JOIN msdb.dbo.sysjobsteps as sjs
					ON sj.job_id = sjs.job_id
			WHERE (sj.job_id = @id
					OR sj.name = @name)
				AND sjs.step_uid = ISNULL(@step_uid, sjs.step_uid)
				AND sjs.step_id = ISNULL(@step_id, sjs.step_id)
				AND ((sj.category_id = 10
						AND sjs.subsystem = N'Distribution')
					OR (sj.category_id = 14
						AND sjs.subsystem = N'Merge')
					OR (sj.category_id = 13
						AND sjs.subsystem = N'LogReader')
					OR (sj.category_id = 15
						AND sjs.subsystem =  N'Snapshot')
					OR (sj.category_id = 19
						AND sjs.subsystem = N'QueueReader')
					OR (@category_id IN (18, 16, 11, 12, 20, 17)
						AND sjs.subsystem = N'TSQL'))

	-- if current user is the owner of the job then all we can
	-- skip straight to ensuring this is a replication job... if
	-- not then we need to do some more verification before hand
	IF @owner_sid != SUSER_SID()
	BEGIN
		-- NOTE 1 : For the replagents we check the database name to
		-- ensure that the user is attempting to change a job related
		-- to the current db. All replagent job's relate to current db
		-- they must also be at least a dbo_owner of the current db...
		IF @category_id IN (10, 13, 14, 15, 19)
			AND NOT (@database = DB_NAME()
				AND IS_MEMBER('db_owner') = 1)
		BEGIN
			-- User does not have permission to perform this action.
			RAISERROR(15247, 16, -1)
			RETURN 1
		END
		
		-- NOTE 2 : For these cleanup jobs we expect the
		-- calling user to be a sysadmin or else we fail
		
		-- 18	= REPL-Alert Response
		-- 16	= REPL-Checkup
		-- 11	= REPL-Distribution Cleanup
		-- 12	= REPL-History Cleanup
		-- 20	= Replication
		-- 17	= REPL-Subscription Cleanup
		IF @category_id IN (18, 16, 11, 12, 20, 17)
			AND IS_SRVROLEMEMBER('sysadmin') != 1
		BEGIN
			-- Only members of the sysadmin fixed server role can perform this operation.
			RAISERROR(21089,16,-1)
			RETURN 1
		END
	END
	
	-- Verify that the job to be modified is a repl job
	IF NOT (@category_id = 10
				AND @subsystem = N'Distribution')
		AND NOT (@category_id = 14
			AND @subsystem = N'Merge')
		AND NOT (@category_id = 13
			AND @subsystem = N'LogReader')
		AND NOT (@category_id = 15
			AND @subsystem =  N'Snapshot')
		AND NOT (@category_id = 19
			AND @subsystem = N'QueueReader')
		-- 18	= REPL-Alert Response
		-- 16	= REPL-Checkup
		-- 11	= REPL-Distribution Cleanup
		-- 12	= REPL-History Cleanup
		-- 20	= Replication
		-- 17	= REPL-Subscription Cleanup
		AND NOT (@category_id IN (18, 16, 11, 12, 20, 17)
			AND @subsystem = N'TSQL')
	BEGIN
		-- Only replication jobs or job schedules can be added, modified, dropped or viewed through replication stored procedures.
		RAISERROR(22538, 16, -1)
		RETURN 1
	END

	RETURN 0
END

 
Last revision 2008RTM
See also

  sp_MSchange_repl_job (Procedure)
sp_MSdrop_repl_job (Procedure)
sp_MSenum_replication_job (Procedure)
sp_MSreplhelp_jobhistory (Procedure)
       



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