/*------------------------------------------------------------------------------

SYSDBUPG.SQL

This script upgrades the system database stored procs from the RTM level.
It is used only for system databases which are upgraded in post-RTM servicing.

The script is also run during mkmastr to produce upgraded refresh databases
for inclusion in the Express skus.

Databases which may be updated by this script:
	master
	model
	msdb

Databases not updated by this script:
	mssqlsystemresource
	distmdl
	adventureworks
	adventureworksdw
	user databases

Any changes to the following scripts are made here instead:
	U_TABLES.SQL
	PROCSYST.SQL
	XPSTAR.SQL
	INSTMSDB.SQL
	REPL_MASTER.SQL

Note:
  This script does not apply any sysmessages changes.
  Such changes are delivered via an updated SQLEVN70.RLL instead.


** Copyright (c) Microsoft Corporation.  All rights reserved.

------------------------------------------------------------------------------*/


--------------------------------------------------------------------------------
--	U_TABLES.SQL
--------------------------------------------------------------------------------


--------------------------------------------------------------------------------
--	PROCSYST.SQL
--------------------------------------------------------------------------------

-- Grant SELECT on Shiloh views to PUBLIC (only if they were not explicitely denied)
--
use master
go

set nocount on
set implicit_transactions off
set ansi_nulls on	-- default for osql (consistent for suites)
set quoted_identifier on	-- Force all ye devs to do this correctly!
go

-- temporary grant select to QE dmv
GRANT SELECT on sys.dm_exec_query_resource_semaphores TO PUBLIC
GRANT SELECT on sys.dm_exec_query_memory_grants TO PUBLIC
go

if NOT EXISTS (
	SELECT * FROM sys.database_permissions 
	WHERE 	class = 1 and major_id = object_id (N'master.dbo.syscacheobjects') and 
			minor_id = 0 and grantee_principal_id = 0 and type = 'SL' and state = 'D')
	GRANT SELECT ON syscacheobjects TO PUBLIC;

if NOT EXISTS (
	SELECT * FROM sys.database_permissions 
	WHERE 	class = 1 and major_id = object_id (N'master.dbo.sysperfinfo') and 
			minor_id = 0 and grantee_principal_id = 0 and type = 'SL' and state = 'D')
	GRANT SELECT ON sysperfinfo TO PUBLIC;
go


--------------------------------------------------------------------------------
--	XPSTAR.SQL
--------------------------------------------------------------------------------


--------------------------------------------------------------------------------
--	INSTMSDB.SQL
--------------------------------------------------------------------------------
/**************************************************************/
/* Create auxilary procedure to enable OBD component          */
/**************************************************************/
CREATE PROCEDURE #sp_enable_component     
   @comp_name     sysname, 
   @advopt_old_value    INT OUT, 
   @comp_old_value   INT OUT 
AS
   BEGIN
   SELECT @advopt_old_value=cast(value_in_use as int) from sys.configurations where name = 'show advanced options';
   SELECT @comp_old_value=cast(value_in_use as int) from sys.configurations where name = @comp_name; 
   EXEC sp_configure 'show advanced options',1;
   RECONFIGURE WITH OVERRIDE;
   EXEC sp_configure @comp_name, 1; 
   RECONFIGURE WITH OVERRIDE;
   END
go

CREATE PROCEDURE #sp_restore_component_state 
   @comp_name     sysname, 
   @advopt_old_value    INT, 
   @comp_old_value   INT 
AS
   BEGIN
   EXEC sp_configure @comp_name, @comp_old_value; 
   RECONFIGURE WITH OVERRIDE;
   EXEC sp_configure 'show advanced options',@advopt_old_value;
   RECONFIGURE WITH OVERRIDE;
   END
go

USE msdb
go

-- Change growth type to 10% for MSDB log and data files
declare @growth bigint
declare @is_percent_growth int

SET @growth = (SELECT growth FROM sys.database_files WHERE name = N'MSDBData')
SET @is_percent_growth = (SELECT is_percent_growth FROM sys.database_files WHERE name = N'MSDBData')

-- If data file grows by 256k (this is the RTM setting) change that to 10% 
IF( (@growth IS NOT NULL) AND (@growth = 32) AND 
	(@is_percent_growth IS NOT NULL) AND (@is_percent_growth = 0 ))
BEGIN
	PRINT 'Update [msdb] data file growth to 10%.'
	ALTER DATABASE [msdb] MODIFY FILE (NAME=N'MSDBData', FILEGROWTH=10%)
END


SET @growth = (SELECT growth FROM sys.database_files WHERE name = N'MSDBLog')
SET @is_percent_growth = (SELECT is_percent_growth FROM sys.database_files WHERE name = N'MSDBLog')

-- If log file grows by 256k (this is the RTM setting) change that to 10% 
IF( (@growth IS NOT NULL) AND (@growth = 32) AND 
	(@is_percent_growth IS NOT NULL) AND (@is_percent_growth = 0 ))
BEGIN
	PRINT 'Update [msdb] log file growth to 10%.'
	ALTER DATABASE [msdb] MODIFY FILE (NAME=N'MSDBLog', FILEGROWTH=10%)
END

GO
-- end 'Change growth type ...'


/**************************************************************/
/* drop certificate signature from Agent signed sps           */
/**************************************************************/
BEGIN TRANSACTION
declare @sp sysname
declare @exec_str nvarchar(1024)
declare ms_crs_sps cursor global for select object_name(crypts.major_id) 
   from sys.crypt_properties crypts, sys.certificates certs
   where crypts.thumbprint = certs.thumbprint
   and crypts.class = 1
   and certs.name = '##MS_AgentSigningCertificate##'
open ms_crs_sps
fetch next from ms_crs_sps into @sp
while @@fetch_status = 0
begin
   if exists(select * from sys.objects where name = @sp)
   begin
      print 'Dropping signature from: ' + @sp
      set @exec_str = N'drop signature from ' + quotename(@sp) + N' by certificate [##MS_AgentSigningCertificate##]'
      Execute(@exec_str)
      if (@@error <> 0)
      begin
         declare @err_str nvarchar(1024)
         set @err_str = 'Cannot drop signature from ' + quotename(@sp) + '. Terminating.'
         RAISERROR(@err_str, 20, 127) WITH LOG
         ROLLBACK TRANSACTION
         return
      end
   end
   fetch next from ms_crs_sps into @sp
end
close ms_crs_sps
deallocate ms_crs_sps
COMMIT TRANSACTION
go

/**************************************************************/
/* sp_verify_subsystems                                       */
/**************************************************************/

PRINT ''
PRINT 'Updating procedure sp_verify_subsystems...'
go
ALTER PROCEDURE dbo.sp_verify_subsystems
   @syssubsytems_refresh_needed BIT = 0
AS
BEGIN
  SET NOCOUNT ON
   
  DECLARE @retval         INT
  DECLARE @InstRootPath nvarchar(512)
  DECLARE @ComRootPath nvarchar(512)
  DECLARE @DtsRootPath nvarchar(512)
  DECLARE @DTExec nvarchar(512)
  DECLARE @DTExecExists INT

  IF ( (@syssubsytems_refresh_needed=1) OR (NOT EXISTS(select * from syssubsystems)) )
  BEGIN
     EXEC master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\Setup', N'SQLPath', @InstRootPath OUTPUT
     IF @InstRootPath IS NULL
     BEGIN
       RAISERROR(14658, -1, -1) WITH LOG
       RETURN (1)
     END
     SELECT @InstRootPath = @InstRootPath + N'\binn\'

     EXEC master.dbo.xp_regread N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\Microsoft Sql Server\90', N'VerSpecificRootDir', @ComRootPath OUTPUT
     IF @ComRootPath IS NULL
     BEGIN
       RAISERROR(14659, -1, -1) WITH LOG
       RETURN(1)
     END

     EXEC master.dbo.xp_regread N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSDTS\Setup\DTSPath', N'', @DtsRootPath OUTPUT, N'no_output'
     IF (@DtsRootPath IS NOT NULL)
     BEGIN
       SELECT @DtsRootPath  = @DtsRootPath  + N'Binn\'
       SELECT @DTExec = @DtsRootPath + N'DTExec.exe'
       CREATE TABLE #t (file_exists int, is_directory int, parent_directory_exists int)
	   INSERT #t EXEC xp_fileexist @DTExec
       SELECT TOP 1 @DTExecExists=file_exists from #t
       DROP TABLE #t
       IF ((@DTExecExists IS NULL) OR (@DTExecExists = 0))
         SET @DtsRootPath = NULL
     END

     SELECT @ComRootPath  = @ComRootPath  + N'COM\'

     -- Procedure must start its own transaction if we don't have one already.
     DECLARE @TranCounter INT;
     SET @TranCounter = @@TRANCOUNT;
     IF @TranCounter = 0
     BEGIN
        BEGIN TRANSACTION;
     END

     -- Obtain processor count to determine maximum number of threads per subsystem
     DECLARE @xp_results TABLE
     (
     id              INT           NOT NULL,
     name            NVARCHAR(30)  COLLATE database_default NOT NULL,
     internal_value  INT           NULL,
     character_value NVARCHAR(212) COLLATE database_default NULL
     )
     INSERT INTO @xp_results
     EXECUTE master.dbo.xp_msver

     DECLARE @processor_count INT
     SELECT @processor_count = internal_value from @xp_results where id=16 -- ProcessorCount

     -- Modify database.
     BEGIN TRY

       --create subsystems
       --TSQL subsystem
       IF NOT EXISTS(SELECT * FROM syssubsystems WHERE subsystem = N'TSQL')
       INSERT syssubsystems
       VALUES
       (
          1, N'TSQL',14556, FORMATMESSAGE(14557), FORMATMESSAGE(14557), FORMATMESSAGE(14557), FORMATMESSAGE(14557), FORMATMESSAGE(14557), 20 * @processor_count
       )
       --ActiveScripting subsystem
       IF NOT EXISTS(SELECT * FROM syssubsystems WHERE subsystem = N'ActiveScripting')
       INSERT syssubsystems
       VALUES
       (
          2, N'ActiveScripting',  14555, @InstRootPath + N'SQLATXSS90.DLL',NULL,N'ActiveScriptStart',N'ActiveScriptEvent',N'ActiveScriptStop', 10 * @processor_count
       )

       --CmdExec subsystem
       IF NOT EXISTS(SELECT * FROM syssubsystems WHERE subsystem = N'CmdExec')
       INSERT syssubsystems
       VALUES
       (
          3, N'CmdExec', 14550, @InstRootPath + N'SQLCMDSS90.DLL',NULL,N'CmdExecStart',N'CmdEvent',N'CmdExecStop', 10 * @processor_count
       )

       --Snapshot subsystem
       IF NOT EXISTS(SELECT * FROM syssubsystems WHERE subsystem = N'Snapshot')
       INSERT syssubsystems
       VALUES
       (
          4, N'Snapshot',   14551, @InstRootPath + N'SQLREPSS90.DLL', @ComRootPath + N'SNAPSHOT.EXE', N'ReplStart',N'ReplEvent',N'ReplStop',100 * @processor_count
       )

       --LogReader subsystem
       IF NOT EXISTS(SELECT * FROM syssubsystems WHERE subsystem = N'LogReader')
       INSERT syssubsystems
       VALUES
       (
          5, N'LogReader',  14552, @InstRootPath + N'SQLREPSS90.DLL', @ComRootPath + N'logread.exe',N'ReplStart',N'ReplEvent',N'ReplStop',25 * @processor_count
       )

       --Distribution subsystem
       IF NOT EXISTS(SELECT * FROM syssubsystems WHERE subsystem = N'Distribution')
       INSERT syssubsystems
       VALUES
       (
          6, N'Distribution',  14553, @InstRootPath + N'SQLREPSS90.DLL', @ComRootPath + N'DISTRIB.EXE',N'ReplStart',N'ReplEvent',N'ReplStop',100 * @processor_count
       )

       --Merge subsystem
       IF NOT EXISTS(SELECT * FROM syssubsystems WHERE subsystem = N'Merge')
       INSERT syssubsystems
       VALUES
       (
          7, N'Merge',   14554, @InstRootPath + N'SQLREPSS90.DLL',@ComRootPath + N'REPLMERG.EXE',N'ReplStart',N'ReplEvent',N'ReplStop',100 * @processor_count
       )

       --QueueReader subsystem
       IF NOT EXISTS(SELECT * FROM syssubsystems WHERE subsystem = N'QueueReader')
       INSERT syssubsystems
       VALUES
       (
          8, N'QueueReader',   14581, @InstRootPath + N'sqlrepss90.dll',@ComRootPath + N'qrdrsvc.exe',N'ReplStart',N'ReplEvent',N'ReplStop',100 * @processor_count
       )

       --ANALYSISQUERY subsystem
       IF NOT EXISTS(SELECT * FROM syssubsystems WHERE subsystem = N'ANALYSISQUERY')
       INSERT syssubsystems
       VALUES
       (
          9, N'ANALYSISQUERY', 14513, @InstRootPath + N'SQLOLAPSS90.DLL',NULL,N'OlapStart',N'OlapQueryEvent',N'OlapStop',100 * @processor_count
       )

       --ANALYSISCOMMAND subsystem
       IF NOT EXISTS(SELECT * FROM syssubsystems WHERE subsystem = N'ANALYSISCOMMAND')
       INSERT syssubsystems
       VALUES
       (
          10, N'ANALYSISCOMMAND', 14514, @InstRootPath + N'SQLOLAPSS90.DLL',NULL,N'OlapStart',N'OlapCommandEvent',N'OlapStop',100 * @processor_count
       )

       IF(@DtsRootPath IS NOT NULL)
       BEGIN
          --DTS subsystem
          IF (NOT EXISTS(SELECT * FROM syssubsystems WHERE subsystem = N'SSIS') )
             INSERT syssubsystems
             VALUES
             (
                11, N'SSIS', 14538, @InstRootPath + N'SQLDTSSS90.DLL',@DtsRootPath + N'DTExec.exe',N'DtsStart',N'DtsEvent',N'DtsStop',100 * @processor_count
             )
          ELSE
             UPDATE syssubsystems SET agent_exe = @DtsRootPath + N'DTExec.exe' WHERE subsystem = N'SSIS'
       END
       ELSE
       BEGIN
          IF EXISTS(SELECT * FROM syssubsystems WHERE subsystem = N'SSIS')
            DELETE FROM syssubsystems WHERE subsystem = N'SSIS' 
       END

   END TRY
   BEGIN CATCH

       DECLARE @ErrorMessage NVARCHAR(400)
       DECLARE @ErrorSeverity INT
       DECLARE @ErrorState INT

       SELECT @ErrorMessage = ERROR_MESSAGE()
       SELECT @ErrorSeverity = ERROR_SEVERITY()
       SELECT @ErrorState = ERROR_STATE()

       -- Roll back the transaction that we started if we are not nested
       IF @TranCounter = 0
       BEGIN
         ROLLBACK TRANSACTION;
       END
       -- if we are nested inside another transaction just raise the 
       -- error and let the outer transaction do the rollback
       RAISERROR (@ErrorMessage, -- Message text.
                   @ErrorSeverity, -- Severity.
                   @ErrorState -- State.
                   )
       RETURN (1)                  
     END CATCH
  END --(NOT EXISTS(select * from syssubsystems))
  
  -- commit the transaction we started
  IF @TranCounter = 0
  BEGIN
    COMMIT TRANSACTION;
  END
  
  RETURN(0) -- Success
END
go

-- Refresh Sybsystem list
-- if SSIS is not installed but SSMS is installed this call will make
-- SSIS subsystem available to Agent jobs
exec sp_verify_subsystems 1
go

/**************************************************************/
/* SP_SQLAGENT_HAS_SERVER_ACCESS                              */
/**************************************************************/

PRINT ''
PRINT 'Creating procedure sp_sqlagent_has_server_access...'
go
IF (EXISTS (SELECT *
            FROM msdb.dbo.sysobjects
            WHERE (name = 'sp_sqlagent_has_server_access')
              AND (type = 'P')))
  DROP PROCEDURE sp_sqlagent_has_server_access
go
CREATE PROCEDURE sp_sqlagent_has_server_access
  @login_name         sysname = NULL,
  @is_sysadmin_member INT     = NULL OUTPUT
AS
BEGIN
  DECLARE @has_server_access BIT
  DECLARE @is_sysadmin       BIT
  DECLARE @actual_login_name sysname
  DECLARE @cachedate         DATETIME

  SET NOCOUNT ON

  SELECT @cachedate = NULL

  -- remove expired entries from the cache
  DELETE msdb.dbo.syscachedcredentials
  WHERE  DATEDIFF(MINUTE, cachedate, GETDATE()) >= 29

  -- query the cache
  SELECT  @is_sysadmin = is_sysadmin_member,
          @has_server_access = has_server_access,
          @cachedate = cachedate
  FROM    msdb.dbo.syscachedcredentials
  WHERE   login_name = @login_name
  AND     DATEDIFF(MINUTE, cachedate, GETDATE()) < 29

  IF (@cachedate IS NOT NULL)
  BEGIN
    -- no output variable
    IF (@is_sysadmin_member IS NULL)
    BEGIN
      -- Return result row
      SELECT has_server_access = @has_server_access,
             is_sysadmin       = @is_sysadmin,
             actual_login_name = @login_name
      RETURN
    END
    ELSE
    BEGIN
      SELECT @is_sysadmin_member = @is_sysadmin
      RETURN
    END
  END -- select from cache

  -- Set defaults
  SELECT @has_server_access = 0
  SELECT @is_sysadmin = 0
  SELECT @actual_login_name = FORMATMESSAGE(14205)

  IF (@login_name IS NULL)
  BEGIN
    SELECT has_server_access = 1,
           is_sysadmin       = IS_SRVROLEMEMBER(N'sysadmin'),
           actual_login_name = SUSER_SNAME()
    RETURN
  END

  IF (@login_name LIKE '%\%')
  BEGIN
    -- Handle the LocalSystem account ('NT AUTHORITY\SYSTEM') as a special case
    IF (UPPER(@login_name collate SQL_Latin1_General_CP1_CS_AS) = N'NT AUTHORITY\SYSTEM')
    BEGIN
      IF (EXISTS (SELECT *
                  FROM master.dbo.syslogins
                  WHERE (UPPER(loginname collate SQL_Latin1_General_CP1_CS_AS) = N'BUILTIN\ADMINISTRATORS')))
      BEGIN
        SELECT @has_server_access = hasaccess,
               @is_sysadmin = sysadmin,
               @actual_login_name = loginname
        FROM master.dbo.syslogins
        WHERE (UPPER(loginname collate SQL_Latin1_General_CP1_CS_AS) = N'BUILTIN\ADMINISTRATORS')
      END
    END
    ELSE
    BEGIN
      -- Check if the NT login has been explicitly denied access
      IF (EXISTS (SELECT *
                  FROM master.dbo.syslogins
                  WHERE (loginname = @login_name)
                    AND (denylogin = 1)))
      BEGIN
        SELECT @has_server_access = 0,
               @is_sysadmin = sysadmin,
               @actual_login_name = loginname
        FROM master.dbo.syslogins
        WHERE (loginname = @login_name)
      END
      ELSE
      BEGIN
        -- declare table variable for storing results
        DECLARE @xp_results TABLE
        (
        account_name      sysname      COLLATE database_default NOT NULL PRIMARY KEY,
        type              NVARCHAR(10) COLLATE database_default NOT NULL,
        privilege         NVARCHAR(10) COLLATE database_default NOT NULL,
        mapped_login_name sysname      COLLATE database_default NOT NULL,
        permission_path   sysname      COLLATE database_default NULL
        )

        -- Call xp_logininfo to determine server access
        INSERT INTO @xp_results
        EXECUTE master.dbo.xp_logininfo @login_name

        SELECT @has_server_access = CASE COUNT(*)
                                      WHEN 0 THEN 0
                                      ELSE 1
                                    END
        FROM @xp_results
        SELECT @actual_login_name = mapped_login_name,
               @is_sysadmin = CASE UPPER(privilege collate SQL_Latin1_General_CP1_CS_AS)
                                WHEN 'ADMIN' THEN 1
                                ELSE 0
                             END
        FROM @xp_results
      END
    END
  END
  ELSE
  BEGIN
    -- Standard login
    IF (EXISTS (SELECT *
                FROM master.dbo.syslogins
                WHERE (loginname = @login_name)))
    BEGIN
      SELECT @has_server_access = hasaccess,
             @is_sysadmin = sysadmin,
             @actual_login_name = loginname
      FROM master.dbo.syslogins
      WHERE (loginname = @login_name)
    END
  END

  -- update the cache only if something is found
  IF  (UPPER(@actual_login_name collate SQL_Latin1_General_CP1_CS_AS) <> '(UNKNOWN)')
  BEGIN
    -- Procedure starts its own transaction.
    BEGIN TRANSACTION;
    
    -- Modify database.
    -- use a try catch login to prevent any error when trying 
    -- to insert/update syscachedcredentials table
    -- no need to fail since the job owner has been validated
    BEGIN TRY      
      IF EXISTS (SELECT * FROM msdb.dbo.syscachedcredentials WITH (TABLOCKX) WHERE login_name = @login_name)
      BEGIN
        UPDATE msdb.dbo.syscachedcredentials
        SET    has_server_access = @has_server_access,
              is_sysadmin_member = @is_sysadmin,
              cachedate = GETDATE()
        WHERE  login_name = @login_name
      END
      ELSE
      BEGIN
        INSERT INTO msdb.dbo.syscachedcredentials(login_name, has_server_access, is_sysadmin_member) 
        VALUES(@login_name, @has_server_access, @is_sysadmin)
      END
	  END TRY
	  BEGIN CATCH
		  -- If an error occurred we want to ignore it
	  END CATCH
	  
	  -- The procedure must commit the transaction it started.
	  COMMIT TRANSACTION;  
  END
  
  IF (@is_sysadmin_member IS NULL)
    -- Return result row
    SELECT has_server_access = @has_server_access,
           is_sysadmin       = @is_sysadmin,
           actual_login_name = @actual_login_name
  ELSE
    -- output variable only
    SELECT @is_sysadmin_member = @is_sysadmin
END
go

exec sp_MS_marksystemobject N'sp_sqlagent_has_server_access'
exec sp_AddFunctionalUnitToComponent N'Agent XPs', N'sp_sqlagent_has_server_access'
if (@@error <> 0)
begin
	RAISERROR('Cannot add stored procedure to component. sysdbupg.sql terminating.', 20, 127) WITH LOG
end


/**************************************************************/
/* SP_ADD_JOBSTEP_INTERNAL                                    */
/**************************************************************/

PRINT ''
PRINT 'Updating procedure sp_add_jobstep_internal...'
go
ALTER PROCEDURE dbo.sp_add_jobstep_internal
  @job_id                UNIQUEIDENTIFIER = NULL,   -- Must provide either this or job_name
  @job_name              sysname          = NULL,   -- Must provide either this or job_id
  @step_id               INT              = NULL,   -- The proc assigns a default
  @step_name             sysname,
  @subsystem             NVARCHAR(40)     = N'TSQL',
  @command               NVARCHAR(max)    = NULL,
  @additional_parameters NTEXT            = NULL,
  @cmdexec_success_code  INT              = 0,
  @on_success_action     TINYINT          = 1,      -- 1 = Quit With Success, 2 = Quit With Failure, 3 = Goto Next Step, 4 = Goto Step
  @on_success_step_id    INT              = 0,
  @on_fail_action        TINYINT          = 2,      -- 1 = Quit With Success, 2 = Quit With Failure, 3 = Goto Next Step, 4 = Goto Step
  @on_fail_step_id       INT              = 0,
  @server                sysname          = NULL,
  @database_name         sysname          = NULL,
  @database_user_name    sysname          = NULL,
  @retry_attempts        INT              = 0,      -- No retries
  @retry_interval        INT              = 0,      -- 0 minute interval
  @os_run_priority       INT              = 0,      -- -15 = Idle, -1 = Below Normal, 0 = Normal, 1 = Above Normal, 15 = Time Critical)
  @output_file_name      NVARCHAR(200)    = NULL,
  @flags                 INT              = 0,       --  0 = Normal, 
                                                     --  1 = Encrypted command (read only), 
                                                     --  2 = Append output files (if any), 
                                                     --  4 = Write TSQL step output to step history
                                                     --  8 = Write log to table (overwrite existing history)
                                                     -- 16 = Write log to table (append to existing history)
  @proxy_id               int               = NULL,
  @proxy_name              sysname           = NULL,
  -- mutual exclusive; must specify only one of above 2 parameters to 
  -- identify the proxy. 
  @step_uid UNIQUEIDENTIFIER              = NULL OUTPUT
AS
BEGIN
  DECLARE @retval         INT
  DECLARE @max_step_id    INT
  DECLARE @job_owner_sid  VARBINARY(85)
  DECLARE @subsystem_id   INT
  DECLARE @auto_proxy_name sysname
  SET NOCOUNT ON

  -- Remove any leading/trailing spaces from parameters
  SELECT @step_name          = LTRIM(RTRIM(@step_name))
  SELECT @subsystem          = LTRIM(RTRIM(@subsystem))
  SELECT @server             = LTRIM(RTRIM(@server))
  SELECT @database_name      = LTRIM(RTRIM(@database_name))
  SELECT @database_user_name = LTRIM(RTRIM(@database_user_name))
  SELECT @output_file_name   = LTRIM(RTRIM(@output_file_name))
  SELECT @proxy_name         = LTRIM(RTRIM(@proxy_name))

  -- Turn [nullable] empty string parameters into NULLs
  IF (@server             = N'') SELECT @server             = NULL
  IF (@database_name      = N'') SELECT @database_name      = NULL
  IF (@database_user_name = N'') SELECT @database_user_name = NULL
  IF (@output_file_name   = N'') SELECT @output_file_name   = NULL
  IF (@proxy_name         = N'') SELECT @proxy_name         = NULL

  -- Check authority (only SQLServerAgent can add a step to a non-local job)
  EXECUTE @retval = sp_verify_jobproc_caller @job_id = @job_id, @program_name = N'SQLAgent%'
  IF (@retval <> 0)
    RETURN(@retval)

  EXECUTE @retval = sp_verify_job_identifiers '@job_name',
                                              '@job_id',
                                               @job_name OUTPUT,
                                               @job_id   OUTPUT,
                                               @owner_sid = @job_owner_sid OUTPUT
  IF (@retval <> 0)
    RETURN(1) -- Failure

  IF ((ISNULL(IS_SRVROLEMEMBER(N'sysadmin'), 0) <> 1) AND
      (SUSER_SID() <> @job_owner_sid))
  BEGIN
     RAISERROR(14525, -1, -1)
     RETURN(1) -- Failure
  END
  

  -- check proxy identifiers only if a proxy has been provided
  IF (@proxy_id IS NOT NULL) or (@proxy_name IS NOT NULL)
  BEGIN
    EXECUTE @retval = sp_verify_proxy_identifiers '@proxy_name',
                                                  '@proxy_id',
                                                   @proxy_name OUTPUT,
                                                   @proxy_id   OUTPUT
    IF (@retval <> 0)
      RETURN(1) -- Failure
   END

  -- Default step id (if not supplied)
  IF (@step_id IS NULL)
  BEGIN
    SELECT @step_id = ISNULL(MAX(step_id), 0) + 1
    FROM msdb.dbo.sysjobsteps
    WHERE (job_id = @job_id)
  END

  -- Check parameters
  EXECUTE @retval = sp_verify_jobstep @job_id,
                                      @step_id,
                                      @step_name,
                                      @subsystem,
                                      @command,
                                      @server,
                                      @on_success_action,
                                      @on_success_step_id,
                                      @on_fail_action,
                                      @on_fail_step_id,
                                      @os_run_priority,
                                      @database_name      OUTPUT,
                                      @database_user_name OUTPUT,
                                      @flags,
                                      @output_file_name,
                                               @proxy_id

  IF (@retval <> 0)
    RETURN(1) -- Failure

  -- Get current maximum step id
  SELECT @max_step_id = ISNULL(MAX(step_id), 0)
  FROM msdb.dbo.sysjobsteps
  WHERE (job_id = @job_id)

  DECLARE @TranCounter INT;
  SET @TranCounter = @@TRANCOUNT;
  IF @TranCounter = 0
  BEGIN
	  -- start our own transaction if there is no outer transaction
      BEGIN TRANSACTION;
  END
  
  -- Modify database.
  BEGIN TRY
    -- Update the job's version/last-modified information
    UPDATE msdb.dbo.sysjobs
    SET version_number = version_number + 1,
        date_modified = GETDATE()
    WHERE (job_id = @job_id)

    -- Adjust step id's (unless the new step is being inserted at the 'end')
    -- NOTE: We MUST do this before inserting the step.
    IF (@step_id <= @max_step_id)
    BEGIN
      UPDATE msdb.dbo.sysjobsteps
      SET step_id = step_id + 1
      WHERE (step_id >= @step_id)
        AND (job_id = @job_id)

      -- Clean up OnSuccess/OnFail references
      UPDATE msdb.dbo.sysjobsteps
      SET on_success_step_id = on_success_step_id + 1
      WHERE (on_success_step_id >= @step_id)
        AND (job_id = @job_id)

      UPDATE msdb.dbo.sysjobsteps
      SET on_fail_step_id = on_fail_step_id + 1
      WHERE (on_fail_step_id >= @step_id)
        AND (job_id = @job_id)

      UPDATE msdb.dbo.sysjobsteps
      SET on_success_step_id = 0,
          on_success_action = 1  -- Quit With Success
      WHERE (on_success_step_id = @step_id)
        AND (job_id = @job_id)

      UPDATE msdb.dbo.sysjobsteps
      SET on_fail_step_id = 0,
          on_fail_action = 2     -- Quit With Failure
      WHERE (on_fail_step_id = @step_id)
        AND (job_id = @job_id)
    END

    SELECT @step_uid = NEWID()

    -- Insert the step
    INSERT INTO msdb.dbo.sysjobsteps
           (job_id,
            step_id,
            step_name,
            subsystem,
            command,
            flags,
            additional_parameters,
            cmdexec_success_code,
            on_success_action,
            on_success_step_id,
            on_fail_action,
            on_fail_step_id,
            server,
            database_name,
            database_user_name,
            retry_attempts,
            retry_interval,
            os_run_priority,
            output_file_name,
            last_run_outcome,
            last_run_duration,
            last_run_retries,
            last_run_date,
            last_run_time,
            proxy_id,
         step_uid)
    VALUES (@job_id,
            @step_id,
            @step_name,
            @subsystem,
            @command,
            @flags,
            @additional_parameters,
            @cmdexec_success_code,
            @on_success_action,
            @on_success_step_id,
            @on_fail_action,
            @on_fail_step_id,
            @server,
            @database_name,
            @database_user_name,
            @retry_attempts,
            @retry_interval,
            @os_run_priority,
            @output_file_name,
            0,
            0,
            0,
            0,
            0,
         @proxy_id,
         @step_uid)
         
  IF @TranCounter = 0
  BEGIN
	  -- start our own transaction if there is no outer transaction
      COMMIT TRANSACTION;
  END

  END TRY
  BEGIN CATCH

      -- Prepare tp echo error information to the caller.
      DECLARE @ErrorMessage NVARCHAR(400)
      DECLARE @ErrorSeverity INT
      DECLARE @ErrorState INT

      SELECT @ErrorMessage = ERROR_MESSAGE()
      SELECT @ErrorSeverity = ERROR_SEVERITY()
      SELECT @ErrorState = ERROR_STATE()
      
      IF @TranCounter = 0
      BEGIN
          -- Transaction started in procedure.
          -- Roll back complete transaction.
          ROLLBACK TRANSACTION;
      END
      RAISERROR (@ErrorMessage, -- Message text.
                  @ErrorSeverity, -- Severity.
                  @ErrorState -- State.
                  )
      RETURN (1)                  
  END CATCH
  
  -- Make sure that SQLServerAgent refreshes the job if the 'Has Steps' property has changed
  IF ((SELECT COUNT(*)
       FROM msdb.dbo.sysjobsteps
       WHERE (job_id = @job_id)) = 1)
  BEGIN
    -- NOTE: We only notify SQLServerAgent if we know the job has been cached
    IF (EXISTS (SELECT *
                FROM msdb.dbo.sysjobservers
                WHERE (job_id = @job_id)
                  AND (server_id = 0)))
      EXECUTE msdb.dbo.sp_sqlagent_notify @op_type       = N'J',
                                            @job_id      = @job_id,
                                            @action_type = N'U'
  END

  -- For a multi-server job, push changes to the target servers
  IF (EXISTS (SELECT *
              FROM msdb.dbo.sysjobservers
              WHERE (job_id = @job_id)
                AND (server_id <> 0)))
  BEGIN
    EXECUTE msdb.dbo.sp_post_msx_operation 'INSERT', 'JOB', @job_id
  END

  RETURN(0) -- Success
END
go

/**************************************************************/
/* SP_SQLAGENT_REFRESH_JOB                                    */
/**************************************************************/

PRINT ''
PRINT 'Updating procedure sp_sqlagent_refresh_job...'
go
ALTER PROCEDURE sp_sqlagent_refresh_job
  @job_id      UNIQUEIDENTIFIER = NULL,
  @server_name sysname          = NULL -- This parameter allows a TSX to use this SP when updating a job
AS
BEGIN
  DECLARE @server_id INT

  SET NOCOUNT ON

  IF (@server_name IS NULL) OR (UPPER(@server_name collate SQL_Latin1_General_CP1_CS_AS) = '(LOCAL)')
    SELECT @server_name = CONVERT(sysname, SERVERPROPERTY('ServerName'))

  SELECT @server_name = UPPER(@server_name)

  SELECT @server_id = server_id
  FROM msdb.dbo.systargetservers_view
  WHERE (UPPER(server_name) = ISNULL(@server_name, UPPER(CONVERT(sysname, SERVERPROPERTY('ServerName')))))

  SELECT @server_id = ISNULL(@server_id, 0)

  SELECT sjv.job_id,
         sjv.name,
         sjv.enabled,
         sjv.start_step_id,
         owner = dbo.SQLAGENT_SUSER_SNAME(sjv.owner_sid),
         sjv.notify_level_eventlog,
         sjv.notify_level_email,
         sjv.notify_level_netsend,
         sjv.notify_level_page,
         sjv.notify_email_operator_id,
         sjv.notify_netsend_operator_id,
         sjv.notify_page_operator_id,
         sjv.delete_level,
         has_step = (SELECT COUNT(*)
                     FROM msdb.dbo.sysjobsteps sjst
                     WHERE (sjst.job_id = sjv.job_id)),
         sjv.version_number,
         last_run_date = ISNULL(sjs.last_run_date, 0),
         last_run_time = ISNULL(sjs.last_run_time, 0),
         sjv.originating_server,
         sjv.description,
         agent_account = CASE sjv.owner_sid
              WHEN 0xFFFFFFFF THEN 1
              ELSE                 0
         END
  FROM msdb.dbo.sysjobservers sjs,
	   msdb.dbo.sysjobs_view  sjv
           
  WHERE ((@job_id IS NULL) OR (@job_id = sjv.job_id))
    AND (sjv.job_id = sjs.job_id)
    AND (sjs.server_id = @server_id)
  ORDER BY sjv.job_id
  OPTION (FORCE ORDER)

  RETURN(@@error) -- 0 means success
END
go
	
/**************************************************************/
/* SP_DELETE_JOB                                              */
/**************************************************************/

PRINT ''
PRINT 'Updating procedure sp_delete_job...'
go

ALTER PROCEDURE sp_delete_job
  @job_id               UNIQUEIDENTIFIER = NULL, -- If provided should NOT also provide job_name
  @job_name             sysname          = NULL, -- If provided should NOT also provide job_id
  @originating_server      sysname         = NULL, -- Reserved (used by SQLAgent)
  @delete_history       BIT              = 1,    -- Reserved (used by SQLAgent)
  @delete_unused_schedule   BIT              = 1     -- For backward compatibility schedules are deleted by default if they are not 
                                        -- being used by another job. With the introduction of reusable schedules in V9 
                                        -- callers should set this to 0 so the schedule will be preserved for reuse.
AS
BEGIN
  DECLARE @current_msx_server sysname
  DECLARE @bMSX_job           BIT
  DECLARE @retval             INT
  DECLARE @local_machine_name sysname
  DECLARE @category_id        INT
  DECLARE @job_owner_sid      VARBINARY(85)
  
  SET NOCOUNT ON
  -- Remove any leading/trailing spaces from parameters
  SELECT @originating_server = UPPER(LTRIM(RTRIM(@originating_server)))

  -- Turn [nullable] empty string parameters into NULLs
  IF (@originating_server = N'') SELECT @originating_server = NULL

  -- Change server name to always reflect real servername or servername\instancename
  IF (@originating_server IS NOT NULL AND @originating_server = '(LOCAL)')
    SELECT @originating_server = UPPER(CONVERT(sysname, SERVERPROPERTY('ServerName')))

  IF ((@job_id IS NOT NULL) OR (@job_name IS NOT NULL))
  BEGIN
    EXECUTE @retval = sp_verify_job_identifiers '@job_name',
                                                '@job_id',
                                                 @job_name OUTPUT,
                                                 @job_id   OUTPUT,
                                                 @owner_sid = @job_owner_sid OUTPUT
    IF (@retval <> 0)
      RETURN(1) -- Failure

  END

  -- We need either a job name or a server name, not both
  IF ((@job_name IS NULL)     AND (@originating_server IS NULL)) OR
     ((@job_name IS NOT NULL) AND (@originating_server IS NOT NULL))
  BEGIN
    RAISERROR(14279, -1, -1)
    RETURN(1) -- Failure
  END

  -- Get category to see if it is a misc. replication agent. @category_id will be
  -- NULL if there is no @job_id.
  select @category_id = category_id from msdb.dbo.sysjobs where job_id = @job_id

  -- If job name was given, determine if the job is from an MSX
  IF (@job_id IS NOT NULL)
  BEGIN
    SELECT @bMSX_job = CASE UPPER(originating_server)
                         WHEN UPPER(CONVERT(sysname, SERVERPROPERTY('ServerName'))) THEN 0
                         ELSE 1
                       END
    FROM msdb.dbo.sysjobs_view
    WHERE (job_id = @job_id)
  END

  -- If server name was given, warn user if different from current MSX
  IF (@originating_server IS NOT NULL)
  BEGIN
    EXECUTE @retval = master.dbo.xp_getnetname @local_machine_name OUTPUT
    IF (@retval <> 0)
      RETURN(1) -- Failure

    IF ((@originating_server = UPPER(CONVERT(sysname, SERVERPROPERTY('ServerName')))) OR (@originating_server = UPPER(@local_machine_name)))
      SELECT @originating_server = UPPER(CONVERT(sysname, SERVERPROPERTY('ServerName')))

    EXECUTE master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',
                                           N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent',
                                           N'MSXServerName',
                                           @current_msx_server OUTPUT,
                                           N'no_output'

    SELECT @current_msx_server = UPPER(@current_msx_server)
    -- If server name was given but it's not the current MSX, print a warning
    SELECT @current_msx_server = LTRIM(RTRIM(@current_msx_server))
    IF ((@current_msx_server IS NOT NULL) AND (@current_msx_server <> N'') AND (@originating_server <> @current_msx_server))
      RAISERROR(14224, 0, 1, @current_msx_server)
  END

  -- Check authority (only SQLServerAgent can delete a non-local job)
  IF (((@originating_server IS NOT NULL) AND (@originating_server <> UPPER(CONVERT(sysname, SERVERPROPERTY('ServerName'))))) OR (@bMSX_job = 1)) AND
     (PROGRAM_NAME() NOT LIKE N'SQLAgent%')
  BEGIN
    RAISERROR(14274, -1, -1)
    RETURN(1) -- Failure
  END
  
  -- Check permissions beyond what's checked by the sysjobs_view
  -- SQLAgentReader and SQLAgentOperator roles that can see all jobs
  -- cannot delete jobs they do not own
  IF (@job_id IS NOT NULL)
  BEGIN
   IF (@job_owner_sid <> SUSER_SID()                     -- does not own the job
       AND (ISNULL(IS_SRVROLEMEMBER(N'sysadmin'), 0) <> 1)) -- is not sysadmin
   BEGIN
     RAISERROR(14525, -1, -1);
     RETURN(1) -- Failure
    END
  END

  -- Do the delete (for a specific job)
  IF (@job_id IS NOT NULL)
  BEGIN
    -- Note: This temp table is referenced by msdb.dbo.sp_delete_job_references
    CREATE TABLE #temp_jobs_to_delete (job_id UNIQUEIDENTIFIER NOT NULL, job_is_cached INT NOT NULL)

    DECLARE @temp_schedules_to_delete TABLE (schedule_id INT NOT NULL)  

    INSERT INTO #temp_jobs_to_delete
    SELECT job_id, (SELECT COUNT(*)
                    FROM msdb.dbo.sysjobservers
                    WHERE (job_id = @job_id)
                      AND (server_id = 0))
    FROM msdb.dbo.sysjobs_view
    WHERE (job_id = @job_id)

    -- Check if we have any work to do
    IF (NOT EXISTS (SELECT *
                    FROM #temp_jobs_to_delete))
    BEGIN
      DROP TABLE #temp_jobs_to_delete
      RETURN(0) -- Success
    END

    -- Post the delete to any target servers (need to do this BEFORE deleting the job itself,
    -- but AFTER clearing all all pending download instructions).  Note that if the job is
    -- NOT a multi-server job then sp_post_msx_operation will catch this and will do nothing.
    DELETE FROM msdb.dbo.sysdownloadlist
    WHERE (object_id = @job_id)
    EXECUTE msdb.dbo.sp_post_msx_operation 'DELETE', 'JOB', @job_id

    -- Must do this before deleting the job itself since sp_sqlagent_notify does a lookup on sysjobs_view
    -- Note: Don't notify agent in this call. It is done after the transaction is committed
    --       just in case this job is in the process of deleting itself
    EXECUTE msdb.dbo.sp_delete_job_references @notify_sqlagent = 0

    -- Delete all traces of the job
    BEGIN TRANSACTION

   --Get the schedules to delete before deleting records from sysjobschedules
    IF(@delete_unused_schedule = 1)
    BEGIN
        --Get the list of schedules to delete
        INSERT INTO @temp_schedules_to_delete
        SELECT DISTINCT schedule_id 
        FROM   msdb.dbo.sysschedules
        WHERE (schedule_id IN 
                (SELECT schedule_id
                FROM msdb.dbo.sysjobschedules
                WHERE (job_id = @job_id)))
    END


    DELETE FROM msdb.dbo.sysjobschedules
    WHERE job_id IN (SELECT job_id FROM #temp_jobs_to_delete)
    
    DELETE FROM msdb.dbo.sysjobservers
    WHERE job_id IN (SELECT job_id FROM #temp_jobs_to_delete)

    DELETE FROM msdb.dbo.sysjobsteps
    WHERE job_id IN (SELECT job_id FROM #temp_jobs_to_delete)

    DELETE FROM msdb.dbo.sysjobs
    WHERE job_id IN (SELECT job_id FROM #temp_jobs_to_delete)
    
    --Delete the schedule(s) if requested to and it isn't being used by other jobs
    IF(@delete_unused_schedule = 1)
    BEGIN
      --Now OK to delete the schedule
      DELETE FROM msdb.dbo.sysschedules
      WHERE schedule_id IN 
        (SELECT schedule_id
         FROM @temp_schedules_to_delete as sdel
         WHERE NOT EXISTS(SELECT * 
                          FROM msdb.dbo.sysjobschedules AS js
                          WHERE (js.schedule_id = sdel.schedule_id)))
    END


    -- Delete the job history if requested    
    IF (@delete_history = 1)
    BEGIN
      DELETE FROM msdb.dbo.sysjobhistory
      WHERE job_id IN (SELECT job_id FROM #temp_jobs_to_delete)
    END
    -- All done
    COMMIT TRANSACTION

    -- Now notify agent to delete the job.
    IF(EXISTS(SELECT * FROM #temp_jobs_to_delete WHERE job_is_cached > 0))
    BEGIN
      DECLARE @nt_user_name   NVARCHAR(100)
      SELECT @nt_user_name = ISNULL(NT_CLIENT(), ISNULL(SUSER_SNAME(), FORMATMESSAGE(14205)))
      --Call the xp directly. sp_sqlagent_notify checks sysjobs_view and the record has already been deleted
      EXEC master.dbo.xp_sqlagent_notify N'J', @job_id, 0, 0, N'D', @nt_user_name, 1, @@trancount, NULL, NULL
    END

  END
  ELSE
  -- Do the delete (for all jobs originating from the specific server)
  IF (@originating_server IS NOT NULL)
  BEGIN
    EXECUTE msdb.dbo.sp_delete_all_msx_jobs @msx_server = @originating_server

    -- NOTE: In this case there is no need to propagate the delete via sp_post_msx_operation
    --       since this type of delete is only ever performed on a TSX.
  END

  IF (OBJECT_ID(N'tempdb.dbo.#temp_jobs_to_delete', 'U') IS NOT NULL)	
    DROP TABLE #temp_jobs_to_delete

  RETURN(0) -- 0 means success
END
go

/**************************************************************/
/* SP_SQLAGENT_IS_SRVROLEMEMBER                               */
/**************************************************************/

PRINT ''
PRINT 'Updating procedure sp_sqlagent_is_srvrolemember...'
go

ALTER PROCEDURE sp_sqlagent_is_srvrolemember
   @role_name sysname, @login_name sysname
AS
BEGIN
  DECLARE @is_member        INT
  SET NOCOUNT ON
  
  IF @role_name IS NULL OR @login_name IS NULL
    RETURN(0)
  
  SELECT @is_member = 0
  --IS_SRVROLEMEMBER works only if the login to be tested is provisioned with sqlserver
  if( @login_name = SUSER_SNAME())
    SELECT @is_member = IS_SRVROLEMEMBER(@role_name)
  else
    SELECT @is_member = IS_SRVROLEMEMBER(@role_name, @login_name)
    
  
  --try to impersonate. A try catch is used because we can have @name as NT groups also
  IF @is_member IS NULL
  BEGIN
    BEGIN TRY
      if( is_srvrolemember('sysadmin') = 1)
      begin
      EXECUTE AS LOGIN = @login_name -- impersonate 
        SELECT @is_member = IS_SRVROLEMEMBER(@role_name)  -- check role membership 
      REVERT -- revert back
      end
    END TRY
    BEGIN CATCH
      SELECT @is_member = 0
    END CATCH
  END
 
  RETURN ISNULL(@is_member,0)
END
GO
 
/**************************************************************/
/* sysmail_delete_mailitems_sp                                */
/**************************************************************/
-----
PRINT 'Updating sysmail_delete_mailitems_sp'
-----
GO
ALTER PROCEDURE sysmail_delete_mailitems_sp
   @sent_before DATETIME   = NULL, -- sent before
   @sent_status varchar(8)   = NULL -- sent status
AS
BEGIN

   SET @sent_status       = LTRIM(RTRIM(@sent_status))
   IF @sent_status           = '' SET @sent_status = NULL

   IF ( (@sent_status IS NOT NULL) AND
         (LOWER(@sent_status collate SQL_Latin1_General_CP1_CS_AS) NOT IN ( 'unsent', 'sent', 'failed', 'retrying') ) )
   BEGIN
      RAISERROR(14266, -1, -1, '@sent_status', 'unsent, sent, failed, retrying')
      RETURN(1) -- Failure
   END

   IF ( @sent_before IS NULL AND @sent_status IS NULL )
   BEGIN
      RAISERROR(14608, -1, -1, '@sent_before', '@sent_status')  
      RETURN(1) -- Failure
   END

   DELETE FROM msdb.dbo.sysmail_allitems 
   WHERE 
        ((@sent_before IS NULL) OR ( send_request_date < @sent_before))
   AND ((@sent_status IS NULL) OR (sent_status = @sent_status))

   DECLARE @localmessage nvarchar(255)
    SET @localmessage = FORMATMESSAGE(14665, SUSER_SNAME(), @@ROWCOUNT)
    exec msdb.dbo.sysmail_logmailevent_sp @event_type=1, @description=@localmessage

END
GO

/**************************************************************/
/* sp_sysmail_activate                                */
/**************************************************************/
-----
PRINT 'Updating procedure sp_sysmail_activate'
-----
GO
-- sp_sysmail_activate : Starts the DatabaseMail process if it isn't already running
--
ALTER PROCEDURE sp_sysmail_activate
AS
BEGIN
    DECLARE @mailDbName sysname
    DECLARE @mailDbId INT
    DECLARE @mailEngineLifeMin INT
    DECLARE @loggingLevel nvarchar(256)
    DECLARE @loggingLevelInt int   
    DECLARE @parameter_value nvarchar(256)
    DECLARE @localmessage nvarchar(max)
    DECLARE @rc INT

    SET NOCOUNT ON
    EXEC sp_executesql @statement = N'RECEIVE TOP(0) * FROM msdb.dbo.ExternalMailQueue'

    EXEC @rc = msdb.dbo.sysmail_help_configure_value_sp @parameter_name = N'DatabaseMailExeMinimumLifeTime', 
                                                        @parameter_value = @parameter_value OUTPUT
    IF(@rc <> 0)
        RETURN (1)

    --ConvertToInt will return the default if @parameter_value is null or config value can't be converted
    --Setting max exe lifetime is 1 week (604800 secs). Can't see a reason for it to ever run longer that this
    SET @mailEngineLifeMin = dbo.ConvertToInt(@parameter_value, 604800, 600) 
    
    --Try and get the optional logging level for the DatabaseMail process
    EXEC msdb.dbo.sysmail_help_configure_value_sp @parameter_name = N'LoggingLevel', 
                                                  @parameter_value = @loggingLevel OUTPUT

    --Convert logging level into string value for passing into XP
    SET @loggingLevelInt = dbo.ConvertToInt(@loggingLevel, 3, 2) 
    IF @loggingLevelInt = 1
       SET @loggingLevel = 'Normal'
    ELSE IF @loggingLevelInt = 3
       SET @loggingLevel = 'Verbose'
    ELSE -- default
       SET @loggingLevel = 'Extended'

    SET @mailDbName = DB_NAME() 
    SET @mailDbId   = DB_ID()

    EXEC @rc = master..xp_sysmail_activate @mailDbId, @mailDbName, @mailEngineLifeMin, @loggingLevel
    IF(@rc <> 0)
    BEGIN
        SET @localmessage = FORMATMESSAGE(14637)
        exec msdb.dbo.sysmail_logmailevent_sp @event_type=3, @description=@localmessage
    END
    ELSE
    BEGIN
        SET @localmessage = FORMATMESSAGE(14638)
        exec msdb.dbo.sysmail_logmailevent_sp @event_type=0, @description=@localmessage
    END

    RETURN @rc
END
GO



/**************************************************************/
/* Sign agent sps and add them to OBD component               */
/*                                                            */
/* Also sign SPs for other components located in MSDB         */
/**************************************************************/
PRINT 'Signing sps ...'
-- Create certificate to sign Agent sps
--
if exists (select * from sys.certificates where name = '##MS_AgentSigningCertificate##')
   drop certificate [##MS_AgentSigningCertificate##]

declare @certError int
dbcc traceon(4606,-1)
create certificate [##MS_AgentSigningCertificate##] 
   encryption by password = 'Yukon90_'
   with subject = 'MS_AgentSigningCertificate'
select @certError = @@error
dbcc traceoff(4606,-1)

IF (@certError <> 0)
   RAISERROR('Cannot create ##MS_AgentSigningCertificate## in msdb.', 20, 127) WITH LOG

go

-- List all of the stored procedures we need to sign
create table #sp_table (name sysname, sign int, comp int)
go
insert into #sp_table values(N'sp_sqlagent_is_srvrolemember',               1, 0)
insert into #sp_table values(N'sp_verify_category_identifiers',               1, 0)
insert into #sp_table values(N'sp_verify_proxy_identifiers',               1, 0)
insert into #sp_table values(N'sp_verify_credential_identifiers',          1, 0)
insert into #sp_table values(N'sp_verify_subsystem_identifiers',           1, 0)
insert into #sp_table values(N'sp_verify_login_identifiers',               1, 0)
insert into #sp_table values(N'sp_verify_proxy',                  1, 0)
insert into #sp_table values(N'sp_add_proxy',                     1, 0)
insert into #sp_table values(N'sp_delete_proxy',                  1, 0)
insert into #sp_table values(N'sp_update_proxy',                  1, 0)
insert into #sp_table values(N'sp_sqlagent_is_member',                  1, 0)
insert into #sp_table values(N'sp_verify_proxy_permissions',               1, 0)
insert into #sp_table values(N'sp_help_proxy',                    1, 0)
insert into #sp_table values(N'sp_grant_proxy_to_subsystem',               1, 0)
insert into #sp_table values(N'sp_grant_login_to_proxy',             1, 0)
insert into #sp_table values(N'sp_revoke_login_from_proxy',             1, 0)
insert into #sp_table values(N'sp_revoke_proxy_from_subsystem',               1, 0)
insert into #sp_table values(N'sp_enum_proxy_for_subsystem',               1, 0)
insert into #sp_table values(N'sp_enum_login_for_proxy',             1, 0)
insert into #sp_table values(N'sp_sqlagent_get_startup_info',              1, 1)
insert into #sp_table values(N'sp_sqlagent_has_server_access',             1, 1)
insert into #sp_table values(N'sp_sem_add_message',                  1, 0)
insert into #sp_table values(N'sp_sem_drop_message',                 1, 0)
insert into #sp_table values(N'sp_get_message_description',             1, 0)
insert into #sp_table values(N'sp_sqlagent_get_perf_counters',             1, 0)
insert into #sp_table values(N'sp_sqlagent_notify',                  1, 1)
insert into #sp_table values(N'sp_is_sqlagent_starting',             1, 1)
insert into #sp_table values(N'sp_verify_job_identifiers',              1, 0)
insert into #sp_table values(N'sp_verify_schedule_identifiers',               1, 0)
insert into #sp_table values(N'sp_verify_jobproc_caller',               1, 0)
insert into #sp_table values(N'sp_downloaded_row_limiter',              1, 1)
insert into #sp_table values(N'sp_post_msx_operation',                  1, 1)
insert into #sp_table values(N'sp_verify_performance_condition',           1, 0)
insert into #sp_table values(N'sp_verify_job_date',                  1, 0)
insert into #sp_table values(N'sp_verify_job_time',                  1, 0)
insert into #sp_table values(N'sp_verify_alert',                  1, 1)
insert into #sp_table values(N'sp_update_alert',                  1, 0)
insert into #sp_table values(N'sp_delete_job_references',               1, 0)
insert into #sp_table values(N'sp_delete_all_msx_jobs',                 1, 0)
insert into #sp_table values(N'sp_generate_target_server_job_assignment_sql',       1, 0)
insert into #sp_table values(N'sp_generate_server_description',               1, 1)
insert into #sp_table values(N'sp_msx_set_account',                  1, 1)
insert into #sp_table values(N'sp_msx_get_account',                  1, 1)
insert into #sp_table values(N'sp_delete_operator',                  1, 0)
insert into #sp_table values(N'sp_msx_defect',                    1, 1)
insert into #sp_table values(N'sp_msx_enlist',                    1, 1)
insert into #sp_table values(N'sp_delete_targetserver',                 1, 0)
insert into #sp_table values(N'sp_get_sqlagent_properties',             1, 1)
insert into #sp_table values(N'sp_set_sqlagent_properties',             1, 1)
insert into #sp_table values(N'sp_add_targetservergroup',               1, 0)
insert into #sp_table values(N'sp_update_targetservergroup',               1, 0)
insert into #sp_table values(N'sp_delete_targetservergroup',               1, 0)
insert into #sp_table values(N'sp_help_targetservergroup',              1, 0)
insert into #sp_table values(N'sp_add_targetsvrgrp_member',             1, 0)
insert into #sp_table values(N'sp_delete_targetsvrgrp_member',             1, 0)
insert into #sp_table values(N'sp_verify_category',                  1, 0)
insert into #sp_table values(N'sp_add_category',                  1, 0)
insert into #sp_table values(N'sp_update_category',                  1, 0)
insert into #sp_table values(N'sp_delete_category',                  1, 0)
insert into #sp_table values(N'sp_help_category',                 1, 0)
insert into #sp_table values(N'sp_help_targetserver',                1, 0)
insert into #sp_table values(N'sp_resync_targetserver',                 1, 0)
insert into #sp_table values(N'sp_purge_jobhistory',                 1, 0)
insert into #sp_table values(N'sp_help_jobhistory',                  1, 0)
insert into #sp_table values(N'sp_add_jobserver',                 1, 0)
insert into #sp_table values(N'sp_delete_jobserver',                 1, 0)
insert into #sp_table values(N'sp_help_jobserver',                1, 0)
insert into #sp_table values(N'sp_help_downloadlist',                1, 0)
insert into #sp_table values(N'sp_enum_sqlagent_subsystems',               1, 0)
insert into #sp_table values(N'sp_enum_sqlagent_subsystems_internal', 1, 0)
insert into #sp_table values(N'sp_verify_subsystem',                 1, 1)
insert into #sp_table values(N'sp_verify_subsystems',                 1, 0)
insert into #sp_table values(N'sp_verify_schedule',                  1, 0)
insert into #sp_table values(N'sp_add_schedule',                  1, 0)
insert into #sp_table values(N'sp_attach_schedule',                  1, 0)
insert into #sp_table values(N'sp_detach_schedule',                  1, 0)
insert into #sp_table values(N'sp_update_schedule',                  1, 0)
insert into #sp_table values(N'sp_delete_schedule',                  1, 0)
insert into #sp_table values(N'sp_get_jobstep_db_username',             1, 0)
insert into #sp_table values(N'sp_verify_jobstep',                1, 0)
insert into #sp_table values(N'sp_add_jobstep_internal',             1, 0)
insert into #sp_table values(N'sp_add_jobstep',                   1, 0)
insert into #sp_table values(N'sp_update_jobstep',                1, 0)
insert into #sp_table values(N'sp_delete_jobstep',                1, 0)
insert into #sp_table values(N'sp_help_jobstep',                  1, 0)
insert into #sp_table values(N'sp_write_sysjobstep_log',             1, 0)
insert into #sp_table values(N'sp_help_jobsteplog',                  1, 0)
insert into #sp_table values(N'sp_delete_jobsteplog',                1, 0)
insert into #sp_table values(N'sp_get_schedule_description',               1, 1)
insert into #sp_table values(N'sp_add_jobschedule',                  1, 0)
insert into #sp_table values(N'sp_update_replication_job_parameter',          1, 0)
insert into #sp_table values(N'sp_update_jobschedule',                  1, 0)
insert into #sp_table values(N'sp_delete_jobschedule',                  1, 0)
insert into #sp_table values(N'sp_help_schedule',                 1, 0)
insert into #sp_table values(N'sp_help_jobschedule',                 1, 0)
insert into #sp_table values(N'sp_verify_job',                    1, 1)
insert into #sp_table values(N'sp_add_job',                    1, 0)
insert into #sp_table values(N'sp_update_job',                    1, 0)
insert into #sp_table values(N'sp_delete_job',                    1, 0)
insert into #sp_table values(N'sp_get_composite_job_info',              1, 1)
insert into #sp_table values(N'sp_help_job',                   1, 0)
insert into #sp_table values(N'sp_help_jobcount ',                1, 0)
insert into #sp_table values(N'sp_help_jobs_in_schedule',               1, 0)
insert into #sp_table values(N'sp_manage_jobs_by_login',             1, 0)
insert into #sp_table values(N'sp_apply_job_to_targets',             1, 0)
insert into #sp_table values(N'sp_remove_job_from_targets',             1, 0)
insert into #sp_table values(N'sp_get_job_alerts',                1, 0)
insert into #sp_table values(N'sp_convert_jobid_to_char',               1, 0)
insert into #sp_table values(N'sp_start_job',                     1, 0)
insert into #sp_table values(N'sp_stop_job',                   1, 0)
insert into #sp_table values(N'sp_cycle_agent_errorlog',             1, 0)
insert into #sp_table values(N'sp_get_chunked_jobstep_params',             1, 0)
insert into #sp_table values(N'sp_check_for_owned_jobs',             1, 0)
insert into #sp_table values(N'sp_check_for_owned_jobsteps',               1, 0)
insert into #sp_table values(N'sp_sqlagent_refresh_job',             1, 0)
insert into #sp_table values(N'sp_jobhistory_row_limiter',              1, 1)
insert into #sp_table values(N'sp_sqlagent_log_jobhistory',             1, 0)
insert into #sp_table values(N'sp_sqlagent_check_msx_version',             1, 0)
insert into #sp_table values(N'sp_sqlagent_probe_msx',                  1, 0)
insert into #sp_table values(N'sp_set_local_time',                1, 1)
insert into #sp_table values(N'sp_multi_server_job_summary',               1, 0)
insert into #sp_table values(N'sp_target_server_summary',               1, 0)
insert into #sp_table values(N'sp_uniquetaskname',                1, 0)
insert into #sp_table values(N'sp_addtask',                    1, 0)
insert into #sp_table values(N'sp_droptask',                   1, 0)
insert into #sp_table values(N'sp_add_alert_internal',                  1, 0)
insert into #sp_table values(N'sp_add_alert',                     1, 0)
insert into #sp_table values(N'sp_delete_alert',                  1, 0)
insert into #sp_table values(N'sp_help_alert',                    1, 0)
insert into #sp_table values(N'sp_verify_operator',                  1, 0)
insert into #sp_table values(N'sp_add_operator',                  1, 0)
insert into #sp_table values(N'sp_update_operator',                  1, 1)
insert into #sp_table values(N'sp_help_operator',                 1, 0)
insert into #sp_table values(N'sp_help_operator_jobs',                  1, 0)
insert into #sp_table values(N'sp_verify_operator_identifiers',               1, 0)
insert into #sp_table values(N'sp_notify_operator',                  1, 0)
insert into #sp_table values(N'sp_verify_notification',                 1, 0)
insert into #sp_table values(N'sp_add_notification',                 1, 0)
insert into #sp_table values(N'sp_update_notification',                 1, 0)
insert into #sp_table values(N'sp_delete_notification',                 1, 0)
insert into #sp_table values(N'sp_help_notification',                1, 0)
insert into #sp_table values(N'sp_help_jobactivity',                 1, 0)
insert into #sp_table values(N'sp_enlist_tsx',                    1, 1)
insert into #sp_table values(N'trig_targetserver_insert',               1, 0)

-- Database Mail configuration procs
insert into #sp_table values(N'sysmail_verify_accountparams_sp',           1, 0)
insert into #sp_table values(N'sysmail_verify_principal_sp',               1, 0)
insert into #sp_table values(N'sysmail_verify_profile_sp',              1, 0)
insert into #sp_table values(N'sysmail_verify_account_sp',              1, 0)
insert into #sp_table values(N'sysmail_add_profile_sp',                 1, 0)
insert into #sp_table values(N'sysmail_update_profile_sp',              1, 0)
insert into #sp_table values(N'sysmail_delete_profile_sp',              1, 0)
insert into #sp_table values(N'sysmail_help_profile_sp',             1, 0)
insert into #sp_table values(N'sysmail_create_user_credential_sp',            1, 0)
insert into #sp_table values(N'sysmail_alter_user_credential_sp',          1, 0)
insert into #sp_table values(N'sysmail_drop_user_credential_sp',           1, 0)
insert into #sp_table values(N'sysmail_add_account_sp',                 1, 0)
insert into #sp_table values(N'sysmail_update_account_sp',              1, 0)
insert into #sp_table values(N'sysmail_delete_account_sp',              1, 0)
insert into #sp_table values(N'sysmail_help_account_sp',             1, 0)
insert into #sp_table values(N'sysmail_help_admin_account_sp',             1, 0)
insert into #sp_table values(N'sysmail_add_profileaccount_sp',             1, 0)
insert into #sp_table values(N'sysmail_update_profileaccount_sp',          1, 0)
insert into #sp_table values(N'sysmail_delete_profileaccount_sp',          1, 0)
insert into #sp_table values(N'sysmail_help_profileaccount_sp',               1, 0)
insert into #sp_table values(N'sysmail_configure_sp',                1, 0)
insert into #sp_table values(N'sysmail_help_configure_sp',              1, 0)
insert into #sp_table values(N'sysmail_help_configure_value_sp',           1, 0)
insert into #sp_table values(N'sysmail_add_principalprofile_sp',           1, 0)
insert into #sp_table values(N'sysmail_update_principalprofile_sp',           1, 0)
insert into #sp_table values(N'sysmail_delete_principalprofile_sp',           1, 0)
insert into #sp_table values(N'sysmail_help_principalprofile_sp',          1, 0)

-- Database Mail: mail host database specific procs
insert into #sp_table values(N'sysmail_start_sp',             1, 2)
insert into #sp_table values(N'sysmail_stop_sp',              1, 2)
insert into #sp_table values(N'sysmail_logmailevent_sp',      1, 0)
insert into #sp_table values(N'sp_SendMailMessage',           1, 0)
insert into #sp_table values(N'sp_isprohibited',              1, 0)
insert into #sp_table values(N'sp_SendMailQueues',            1, 0)
insert into #sp_table values(N'sp_ProcessResponse',           1, 0)
insert into #sp_table values(N'sp_MailItemResultSets',        1, 0)
insert into #sp_table values(N'sp_process_DialogTimer',       1, 0)
insert into #sp_table values(N'sp_readrequest',               1, 0)
insert into #sp_table values(N'sp_GetAttachmentData',         1, 0)
insert into #sp_table values(N'sp_RunMailQuery',              1, 0)
insert into #sp_table values(N'sysmail_help_queue_sp',        1, 0)
insert into #sp_table values(N'sysmail_help_status_sp',       1, 2)
insert into #sp_table values(N'sysmail_delete_mailitems_sp',  1, 0)
insert into #sp_table values(N'sysmail_delete_log_sp',        1, 0)
insert into #sp_table values(N'sp_send_dbmail',               1, 2)
insert into #sp_table values(N'sp_ExternalMailQueueListener', 1, 0)
insert into #sp_table values(N'sp_sysmail_activate',          1, 0)
insert into #sp_table values(N'sp_get_script',                1, 0)

-- Maintenance Plans
insert into #sp_table values(N'sp_maintplan_delete_log',             1, 0)
insert into #sp_table values(N'sp_maintplan_delete_subplan',               1, 0)
insert into #sp_table values(N'sp_maintplan_open_logentry',             1, 0)
insert into #sp_table values(N'sp_maintplan_close_logentry',               1, 0)
insert into #sp_table values(N'sp_maintplan_update_log',             1, 0)
insert into #sp_table values(N'sp_maintplan_update_subplan',               1, 0)
insert into #sp_table values(N'sp_maintplan_delete_plan',               1, 0)
insert into #sp_table values(N'sp_maintplan_start',                  1, 0)
insert into #sp_table values(N'sp_clear_dbmaintplan_by_db',             1, 0)
insert into #sp_table values(N'sp_add_maintenance_plan',             1, 0)
insert into #sp_table values(N'sp_delete_maintenance_plan',             1, 0)
insert into #sp_table values(N'sp_add_maintenance_plan_db',             1, 0)
insert into #sp_table values(N'sp_delete_maintenance_plan_db',             1, 0)
insert into #sp_table values(N'sp_add_maintenance_plan_job',               1, 1)
insert into #sp_table values(N'sp_delete_maintenance_plan_job',               1, 0)
insert into #sp_table values(N'sp_help_maintenance_plan',               1, 0)

-- Log Shipping
insert into #sp_table values(N'sp_add_log_shipping_monitor_jobs',          1, 0)
insert into #sp_table values(N'sp_add_log_shipping_primary',               1, 0)
insert into #sp_table values(N'sp_add_log_shipping_secondary',             1, 0)
insert into #sp_table values(N'sp_delete_log_shipping_monitor_jobs',          1, 0)
insert into #sp_table values(N'sp_delete_log_shipping_primary',               1, 0)
insert into #sp_table values(N'sp_delete_log_shipping_secondary ',            1, 0)
insert into #sp_table values(N'sp_log_shipping_in_sync',             1, 0)
insert into #sp_table values(N'sp_log_shipping_get_date_from_file ',          1, 0)
insert into #sp_table values(N'sp_get_log_shipping_monitor_info',          1, 0)
insert into #sp_table values(N'sp_update_log_shipping_monitor_info',          1, 0)
insert into #sp_table values(N'sp_delete_log_shipping_monitor_info',          1, 0)
insert into #sp_table values(N'sp_remove_log_shipping_monitor_account',          1, 0)
insert into #sp_table values(N'sp_log_shipping_monitor_backup',               1, 0)
insert into #sp_table values(N'sp_log_shipping_monitor_restore',           1, 0)
insert into #sp_table values(N'sp_change_monitor_role',                 1, 0)
insert into #sp_table values(N'sp_create_log_shipping_monitor_account',          1, 0)

-- DTS
insert into #sp_table values(N'sp_get_dtsversion',                1, 0)
insert into #sp_table values(N'sp_make_dtspackagename',                 1, 0)
insert into #sp_table values(N'sp_add_dtspackage',                1, 0)
insert into #sp_table values(N'sp_drop_dtspackage',                  1, 0)
insert into #sp_table values(N'sp_reassign_dtspackageowner',               1, 0)
insert into #sp_table values(N'sp_get_dtspackage',                1, 0)
insert into #sp_table values(N'sp_reassign_dtspackagecategory',               1, 0)
insert into #sp_table values(N'sp_enum_dtspackages',                 1, 0)
insert into #sp_table values(N'sp_add_dtscategory',                  1, 0)
insert into #sp_table values(N'sp_drop_dtscategory',                 1, 0)
insert into #sp_table values(N'sp_modify_dtscategory',                  1, 0)
insert into #sp_table values(N'sp_enum_dtscategories',                  1, 0)
insert into #sp_table values(N'sp_log_dtspackage_begin',             1, 0)
insert into #sp_table values(N'sp_log_dtspackage_end',                  1, 0)
insert into #sp_table values(N'sp_log_dtsstep_begin',                1, 0)
insert into #sp_table values(N'sp_log_dtsstep_end',                  1, 0)
insert into #sp_table values(N'sp_log_dtstask',                   1, 0)
insert into #sp_table values(N'sp_enum_dtspackagelog',                  1, 0)
insert into #sp_table values(N'sp_enum_dtssteplog',                  1, 0)
insert into #sp_table values(N'sp_enum_dtstasklog',                  1, 0)
insert into #sp_table values(N'sp_dump_dtslog_all',                  1, 0)
insert into #sp_table values(N'sp_dump_dtspackagelog',                  1, 0)
insert into #sp_table values(N'sp_dump_dtssteplog',                  1, 0)
insert into #sp_table values(N'sp_dump_dtstasklog',                  1, 0)
insert into #sp_table values(N'sp_dts_addlogentry',                  1, 0)
insert into #sp_table values(N'sp_dts_listpackages',                 1, 0)
insert into #sp_table values(N'sp_dts_listfolders',                  1, 0)
insert into #sp_table values(N'sp_dts_deletepackage',                1, 0)
insert into #sp_table values(N'sp_dts_deletefolder',                 1, 0)
insert into #sp_table values(N'sp_dts_getpackage',                1, 0)
insert into #sp_table values(N'sp_dts_getfolder',                 1, 0)
insert into #sp_table values(N'sp_dts_putpackage',                1, 0)
insert into #sp_table values(N'sp_dts_addfolder',                 1, 0)
insert into #sp_table values(N'sp_dts_renamefolder',                 1, 0)
insert into #sp_table values(N'sp_dts_setpackageroles',                 1, 0)
insert into #sp_table values(N'sp_dts_getpackageroles',                 1, 0)
go

BEGIN TRANSACTION
declare @sp sysname
declare @exec_str nvarchar(1024)
declare @sign int
declare @comp int
declare ms_crs_sps cursor global for select name, sign, comp from #sp_table 
open ms_crs_sps
fetch next from ms_crs_sps into @sp, @sign, @comp  
while @@fetch_status = 0
begin
   if exists(select * from sys.objects where name = @sp)
   begin
      print 'processing sp: ' + @sp
      if (@sign = 1)
      begin
         set @exec_str = N'add signature to ' + @sp + N' by certificate [##MS_AgentSigningCertificate##] with password = ''Yukon90_'''
         Execute(@exec_str)
         if (@@error <> 0)
         begin
            declare @err_str nvarchar(1024)
            set @err_str = 'Cannot sign stored procedure ' + @sp + '. Terminating.'
            RAISERROR(@err_str, 20, 127) WITH LOG
            ROLLBACK TRANSACTION
            return
         end
      end

   end
   fetch next from ms_crs_sps into @sp, @sign, @comp  
end
close ms_crs_sps
deallocate ms_crs_sps
COMMIT TRANSACTION
go
drop table #sp_table
go
-- drop certificate private key
alter certificate [##MS_AgentSigningCertificate##] remove private key

IF (@@error <> 0)
   RAISERROR('Cannot create ##MS_AgentSigningCertificate## in msdb. INSTMSDB.SQL terminating.', 20, 127) WITH LOG
go
--create a temporary database in order to get the path to the 'Data' folder
--because upon upgrade existing database are in temporary folder
IF (EXISTS (SELECT name
                FROM master.dbo.sysdatabases
                WHERE (name = N'temp_MS_AgentSigningCertificate_database')))
BEGIN
  DROP DATABASE temp_MS_AgentSigningCertificate_database
END
go
CREATE DATABASE temp_MS_AgentSigningCertificate_database
go
-- export certificate to master
-- use current directory to persist the file
--
DECLARE @certificate_name NVARCHAR(520)

SELECT @certificate_name = SUBSTRING(filename, 1, CHARINDEX(N'temp_MS_AgentSigningCertificate_database.mdf', filename) - 1) + 
                           CONVERT(NVARCHAR(520), NEWID()) + N'.cer'
  FROM master.dbo.sysaltfiles
  WHERE (name = N'temp_MS_AgentSigningCertificate_database')
  
EXECUTE(N'dump certificate [##MS_AgentSigningCertificate##] to file = ''' + @certificate_name + '''')

IF (@@error <> 0)
   RAISERROR('Cannot dump ##MS_AgentSigningCertificate##. INSTMSDB.SQL terminating.', 20, 127) WITH LOG

use master

if exists (select * from sys.database_principals where name = '##MS_AgentSigningCertificate##')
   drop user [##MS_AgentSigningCertificate##]

if exists (select * from sys.server_principals where name = '##MS_AgentSigningCertificate##')
   drop login [##MS_AgentSigningCertificate##]

if exists (select * from sys.certificates where name = '##MS_AgentSigningCertificate##')
   drop certificate [##MS_AgentSigningCertificate##]

execute(N'create certificate [##MS_AgentSigningCertificate##] from file = ''' + @certificate_name + '''')
IF (@@error <> 0)
   RAISERROR('Cannot create ##MS_AgentSigningCertificate## certificate in master. INSTMSDB.SQL terminating.', 20, 127) WITH LOG

-- create login
--
create login [##MS_AgentSigningCertificate##] from certificate [##MS_AgentSigningCertificate##]
IF (@@error <> 0)
   RAISERROR('Cannot create ##MS_AgentSigningCertificate## login. INSTMSDB.SQL terminating.', 20, 127) WITH LOG

-- create certificate based user for execution granting
--
create user [##MS_AgentSigningCertificate##] for certificate [##MS_AgentSigningCertificate##]
IF (@@error <> 0)
   RAISERROR('Cannot create ##MS_AgentSigningCertificate## user. INSTMSDB.SQL terminating.', 20, 127) WITH LOG

-- enable certificate for OBD
--
exec sys.sp_SetOBDCertificate N'##MS_AgentSigningCertificate##',N'ON'

grant execute to [##MS_AgentSigningCertificate##]

use msdb
go
-- drop temporary database
IF (EXISTS (SELECT name
                FROM master.dbo.sysdatabases
                WHERE (name = N'temp_MS_AgentSigningCertificate_database')))
BEGIN
  DROP DATABASE temp_MS_AgentSigningCertificate_database
END
go

PRINT 'Succesfully signed sps'
--
-- End of signing sps
go

/**************************************************************/
/* Drop auxilary procedure to enable OBD component          */
/**************************************************************/
DROP PROCEDURE #sp_enable_component 
go
DROP PROCEDURE #sp_restore_component_state 
go

--------------------------------------------------------------------------------
--	REPL_MASTER.SQL
--------------------------------------------------------------------------------