create procedure sys.sp_MSdrop_repl_job_unsafe
(
@job_name sysname = NULL,
@job_id binary(16) = NULL,
@job_step_uid uniqueidentifier = NULL
)
WITH EXECUTE AS 'dbo'
AS
BEGIN
DECLARE @retcode int,
@command nvarchar(4000),
@credential sysname,
@proxy_id int,
@subsystem_id int,
@flags int,
@sid varbinary(85),
@login sysname
SELECT @command = NULL,
@credential = NULL,
@proxy_id = NULL,
@subsystem_id = NULL,
@flags = NULL,
@sid = NULL,
@login = NULL
-- Return immediately if this is running on SQLExpress or SQLWeb
IF (sys.fn_MSrepl_editionid () in (22, 40))
RETURN 0
BEGIN TRANSACTION tr_drop_repl_job_internal
SAVE TRANSACTION tr_drop_repl_job_internal
SELECT @credential = sc.name,
@proxy_id = sp.proxy_id,
@subsystem_id = ss.subsystem_id,
@flags = spl.flags,
@sid = spl.sid
FROM msdb.dbo.sysjobs_view as sjv
JOIN msdb.dbo.sysjobsteps as sjs
ON sjv.job_id = sjs.job_id
JOIN msdb.dbo.sysproxies sp
ON sjs.proxy_id = sp.proxy_id
JOIN msdb.dbo.syssubsystems ss
ON sjs.subsystem = ss.subsystem
JOIN sys.credentials sc
ON sp.credential_id = sc.credential_id
-- left join since in sysadmin user case no proxylogin will be found
LEFT JOIN msdb.dbo.sysproxylogin spl
ON sp.proxy_id = spl.proxy_id
WHERE sjv.master_server = 0
AND UPPER(sjv.originating_server) = UPPER(CONVERT(sysname, SERVERPROPERTY('ServerName')))
AND (sjv.job_id = @job_id
OR sjv.name = @job_name)
AND sjs.step_uid = @job_step_uid
IF @@ERROR <> 0 or @retcode <> 0
GOTO UNDO
EXEC @retcode = msdb.dbo.sp_delete_job @job_name = @job_name,
@job_id = @job_id
IF @@ERROR <> 0 or @retcode <> 0
GOTO UNDO
-- we will not drop the proxy, proxy mappings etc if there's
-- still at least 1 job that's still using the given proxy id
IF NOT EXISTS (SELECT *
FROM msdb.dbo.sysjobsteps
WHERE proxy_id = @proxy_id)
BEGIN
IF @proxy_id IS NOT NULL
BEGIN
CREATE TABLE #enm_login_for_proxy (proxy_id int, proxy_name sysname, flags int, name sysname NULL, sid varbinary(85), principal_id int NULL)
INSERT INTO #enm_login_for_proxy
EXEC msdb.dbo.sp_enum_login_for_proxy @proxy_id = @proxy_id
SELECT @login = name
FROM #enm_login_for_proxy
WHERE proxy_id = @proxy_id
AND @sid IS NOT NULL
AND sid = @sid
DROP TABLE #enm_login_for_proxy
IF @login IS NOT NULL
BEGIN
EXEC @retcode = msdb.dbo.sp_revoke_login_from_proxy @name = @login,
@proxy_id = @proxy_id
IF @@ERROR <> 0 OR @retcode <> 0
GOTO UNDO
END
IF @subsystem_id IS NOT NULL
AND EXISTS (SELECT *
FROM msdb.dbo.sysproxysubsystem
WHERE proxy_id = @proxy_id
AND subsystem_id = @subsystem_id)
BEGIN
EXEC @retcode = msdb.dbo.sp_revoke_proxy_from_subsystem @subsystem_id = @subsystem_id,
@proxy_id = @proxy_id
IF @@ERROR <> 0 OR @retcode <> 0
GOTO UNDO
END
IF EXISTS (SELECT *
FROM msdb.dbo.sysproxies
WHERE proxy_id = @proxy_id)
BEGIN
EXEC @retcode = msdb.dbo.sp_delete_proxy @proxy_id = @proxy_id
IF @@ERROR <> 0 OR @retcode <> 0
GOTO UNDO
END
END
IF @credential IS NOT NULL
AND EXISTS (SELECT *
FROM sys.credentials
WHERE name = @credential)
BEGIN
SELECT @command = N'DROP CREDENTIAL ' + QUOTENAME(@credential)
EXEC(@command)
IF @@ERROR <> 0 OR @retcode <> 0
GOTO UNDO
END
END
COMMIT TRANSACTION tr_drop_repl_job_internal
RETURN 0
UNDO:
ROLLBACK TRANSACTION tr_drop_repl_job_internal
COMMIT TRANSACTION
RETURN 1
END