The EXECUTE permission was denied on the object 'xp_sqlagent_enum_jobs', database 'mssqlsystemresource', schema 'sys'.

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SQL Server 2005 Security » The EXECUTE permission was denied on the...

17 posts, Page 2 of 2

««1 2

The EXECUTE permission was denied on the object 'xp_sqlagent_enum_jobs', database 'mssqlsystemresource', schema 'sys'.

Rate Topic

Display Mode

Topic Options

Author

Message

Tony Fountain

Posted Wednesday, October 07, 2009 12:52 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, October 21, 2009 9:31 AM
Points: 146, Visits: 125

Ok, needs debugged but I think this script will do exactly what I need - rebuild the certificates and signatures from scratch. This code was pulled from the instmsdb.sql and sysdbupg.sql installation scripts for SQL Server. This is currently untested.

SET NOCOUNT ON;
GO
USE [msdb];
GO

/* Standard variables */  
DECLARE @ErrorSeverity INT, @ErrorState INT, @ReturnCode INT, @SavedRowCount INT;
DECLARE @StackTrace NVARCHAR(MAX);
DECLARE @ProcedureName SYSNAME; SET @ProcedureName = OBJECT_NAME(@@PROCID);

/* Script specific variables */
DECLARE @SystemProcedureName SYSNAME;
DECLARE @SQL NVARCHAR(1024);
DECLARE @SignFlag INT;

BEGIN TRY
    BEGIN TRANSACTION;

    /* Remove any signatures that might exist */
    DECLARE curObjects CURSOR LOCAL FAST_FORWARD
    FOR 
    SELECT OBJECT_NAME(crypts.major_id) 
    FROM sys.crypt_properties crypts
        INNER JOIN sys.certificates certs
            ON crypts.thumbprint = certs.thumbprint
            AND crypts.class = 1
    WHERE certs.name = '##MS_AgentSigningCertificate##';

    OPEN curObjects;
    FETCH curObjects INTO @SystemProcedureName;

    WHILE (@@FETCH_STATUS = 0)
    BEGIN
       IF EXISTS(SELECT TOP 1 1 FROM sys.objects WHERE name = @SystemProcedureName)
       BEGIN
          RAISERROR('Dropping signature from procedure ''%s''...', 10, 1, @SystemProcedureName) WITH NOWAIT;
          SET @SQL = N'DROP SIGNATURE FROM ' + QUOTENAME(@SystemProcedureName) + N' BY CERTIFICATE [##MS_AgentSigningCertificate##];';
          EXECUTE(@SQL);
          IF (@@ERROR != 0) RAISERROR('Unable to drop signature from ''%s''.', 15, 1, @SystemProcedureName) WITH NOWAIT;
       END

       FETCH curObjects INTO @SystemProcedureName;
    END

    CLOSE curObjects;
    DEALLOCATE curObjects;

    COMMIT TRANSACTION;

    /* Drop certificate if it exists */
    IF EXISTS (SELECT TOP 1 1 FROM [sys].[certificates] WHERE name = '##MS_AgentSigningCertificate##')
        BEGIN
            DROP CERTIFICATE [##MS_AgentSigningCertificate##];
            IF (@@ERROR != 0) RAISERROR('Unable to drop certificate ##MS_AgentSigningCertificate## from msdb.', 15, 1) WITH NOWAIT;
        END

    /* Create certificate again */
    CREATE CERTIFICATE [##MS_AgentSigningCertificate##] 
        ENCRYPTION BY PASSWORD = 'Yukon90_'
        WITH SUBJECT = 'MS_AgentSigningCertificate';
    IF (@@ERROR != 0) RAISERROR('Unable to create certificate ##MS_AgentSigningCertificate## from msdb.', 15, 1) WITH NOWAIT;

    IF OBJECT_ID('tempdb..#sp_table') IS NOT NULL DROP TABLE #sp_table;
    CREATE TABLE #sp_table (Name SYSNAME, SignFlag INT, ComponentFlag INT);

    /* This entire section is taken directly from the instmsdb.sql installation script */
    INSERT INTO #sp_table (Name, SignFlag, ComponentFlag) VALUES(N'sp_sqlagent_is_srvrolemember', 1, 0);
    INSERT INTO #sp_table (Name, SignFlag, ComponentFlag) VALUES(N'sp_verify_category_identifiers', 1, 0);
    INSERT INTO #sp_table (Name, SignFlag, ComponentFlag) VALUES(N'sp_verify_proxy_identifiers', 1, 0);
    INSERT INTO #sp_table (Name, SignFlag, ComponentFlag) VALUES(N'sp_verify_credential_identifiers', 1, 0);
    INSERT INTO #sp_table (Name, SignFlag, ComponentFlag) VALUES(N'sp_verify_subsystem_identifiers', 1, 0);
    INSERT INTO #sp_table (Name, SignFlag, ComponentFlag) VALUES(N'sp_verify_login_identifiers', 1, 0);
    INSERT INTO #sp_table (Name, SignFlag, ComponentFlag) VALUES(N'sp_verify_proxy', 1, 0);
    INSERT INTO #sp_table (Name, SignFlag, ComponentFlag) VALUES(N'sp_add_proxy', 1, 0);
    INSERT INTO #sp_table (Name, SignFlag, ComponentFlag) VALUES(N'sp_delete_proxy', 1, 0);
    INSERT INTO #sp_table (Name, SignFlag, ComponentFlag) VALUES(N'sp_update_proxy', 1, 0);
    INSERT INTO #sp_table (Name, SignFlag, ComponentFlag) VALUES(N'sp_sqlagent_is_member', 1, 0);
    INSERT INTO #sp_table (Name, SignFlag, ComponentFlag) VALUES(N'sp_verify_proxy_permissions', 1, 0);
    INSERT INTO #sp_table (Name, SignFlag, ComponentFlag) VALUES(N'sp_help_proxy', 1, 0);
    INSERT INTO #sp_table (Name, SignFlag, ComponentFlag) VALUES(N'sp_grant_proxy_to_subsystem', 1, 0);
    INSERT INTO #sp_table (Name, SignFlag, ComponentFlag) VALUES(N'sp_grant_login_to_proxy', 1, 0);
    INSERT INTO #sp_table (Name, SignFlag, ComponentFlag) VALUES(N'sp_revoke_login_from_proxy', 1, 0);
    INSERT INTO #sp_table (Name, SignFlag, ComponentFlag) VALUES(N'sp_revoke_proxy_from_subsystem', 1, 0);
    INSERT INTO #sp_table (Name, SignFlag, ComponentFlag) VALUES(N'sp_enum_proxy_for_subsystem', 1, 0);
    INSERT INTO #sp_table (Name, SignFlag, ComponentFlag) VALUES(N'sp_enum_login_for_proxy', 1, 0);
    INSERT INTO #sp_table (Name, SignFlag, ComponentFlag) VALUES(N'sp_sqlagent_get_startup_info', 1, 1);
    INSERT INTO #sp_table (Name, SignFlag, ComponentFlag) VALUES(N'sp_sqlagent_has_server_access', 1, 1);
    INSERT INTO #sp_table (Name, SignFlag, ComponentFlag) VALUES(N'sp_sem_add_message', 1, 0);
    INSERT INTO #sp_table (Name, SignFlag, ComponentFlag) VALUES(N'sp_sem_drop_message', 1, 0);
    INSERT INTO #sp_table (Name, SignFlag, ComponentFlag) VALUES(N'sp_get_message_description', 1, 0);
    INSERT INTO #sp_table (Name, SignFlag, ComponentFlag) VALUES(N'sp_sqlagent_get_perf_counters', 1, 0);
    INSERT INTO #sp_table (Name, SignFlag, ComponentFlag) VALUES(N'sp_sqlagent_notify', 1, 1);
    INSERT INTO #sp_table (Name, SignFlag, ComponentFlag) VALUES(N'sp_is_sqlagent_starting', 1, 1);
    INSERT INTO #sp_table (Name, SignFlag, ComponentFlag) VALUES(N'sp_verify_job_identifiers', 1, 0);
    INSERT INTO #sp_table (Name, SignFlag, ComponentFlag) VALUES(N'sp_verify_schedule_identifiers', 1, 0);
    INSERT INTO #sp_table (Name, SignFlag, ComponentFlag) VALUES(N'sp_verify_jobproc_caller', 1, 0);
    INSERT INTO #sp_table (Name, SignFlag, ComponentFlag) VALUES(N'sp_downloaded_row_limiter', 1, 1);
    INSERT INTO #sp_table (Name, SignFlag, ComponentFlag) VALUES(N'sp_post_msx_operation', 1, 1);
    INSERT INTO #sp_table (Name, SignFlag, ComponentFlag) VALUES(N'sp_verify_performance_condition', 1, 0);
    INSERT INTO #sp_table (Name, SignFlag, ComponentFlag) VALUES(N'sp_verify_job_date', 1, 0);
    INSERT INTO #sp_table (Name, SignFlag, ComponentFlag) VALUES(N'sp_verify_job_time', 1, 0);
    INSERT INTO #sp_table (Name, SignFlag, ComponentFlag) VALUES(N'sp_verify_alert', 1, 1);
    INSERT INTO #sp_table (Name, SignFlag, ComponentFlag) VALUES(N'sp_update_alert', 1, 0);
    INSERT INTO #sp_table (Name, SignFlag, ComponentFlag) VALUES(N'sp_delete_job_references', 1, 0);
    INSERT INTO #sp_table (Name, SignFlag, ComponentFlag) VALUES(N'sp_delete_all_msx_jobs', 1, 0);
    INSERT INTO #sp_table (Name, SignFlag, ComponentFlag) VALUES(N'sp_generate_target_server_job_assignment_sql', 1, 0);
    INSERT INTO #sp_table (Name, SignFlag, ComponentFlag) VALUES(N'sp_generate_server_description', 1, 1);
    INSERT INTO #sp_table (Name, SignFlag, ComponentFlag) VALUES(N'sp_msx_set_account', 1, 1);
    INSERT INTO #sp_table (Name, SignFlag, ComponentFlag) VALUES(N'sp_msx_get_account', 1, 1);
    INSERT INTO #sp_table (Name, SignFlag, ComponentFlag) VALUES(N'sp_delete_operator', 1, 0);
    INSERT INTO #sp_table (Name, SignFlag, ComponentFlag) VALUES(N'sp_msx_defect', 1, 1);
    INSERT INTO #sp_table (Name, SignFlag, ComponentFlag) VALUES(N'sp_msx_enlist', 1, 1);
    INSERT INTO #sp_table (Name, SignFlag, ComponentFlag) VALUES(N'sp_delete_targetserver', 1, 0);
    INSERT INTO #sp_table (Name, SignFlag, ComponentFlag) VALUES(N'sp_get_sqlagent_properties', 1, 1);
    INSERT INTO #sp_table (Name, SignFlag, ComponentFlag) VALUES(N'sp_set_sqlagent_properties', 1, 1);
    INSERT INTO #sp_table (Name, SignFlag, ComponentFlag) VALUES(N'sp_add_targetservergroup', 1, 0);
    INSERT INTO #sp_table (Name, SignFlag, ComponentFlag) VALUES(N'sp_update_targetservergroup', 1, 0);
    INSERT INTO #sp_table (Name, SignFlag, ComponentFlag) VALUES(N'sp_delete_targetservergroup', 1, 0);
    INSERT INTO #sp_table (Name, SignFlag, ComponentFlag) VALUES(N'sp_help_targetservergroup', 1, 0);
    INSERT INTO #sp_table (Name, SignFlag, ComponentFlag) VALUES(N'sp_add_targetsvrgrp_member', 1, 0);
    INSERT INTO #sp_table (Name, SignFlag, ComponentFlag) VALUES(N'sp_delete_targetsvrgrp_member', 1, 0);
    INSERT INTO #sp_table (Name, SignFlag, ComponentFlag) VALUES(N'sp_verify_category', 1, 0);
    INSERT INTO #sp_table (Name, SignFlag, ComponentFlag) VALUES(N'sp_add_category', 1, 0);
    INSERT INTO #sp_table (Name, SignFlag, ComponentFlag) VALUES(N'sp_update_category', 1, 0);
    INSERT INTO #sp_table (Name, SignFlag, ComponentFlag) VALUES(N'sp_delete_category', 1, 0);
    INSERT INTO #sp_table (Name, SignFlag, ComponentFlag) VALUES(N'sp_help_category', 1, 0);
    INSERT INTO #sp_table (Name, SignFlag, ComponentFlag) VALUES(N'sp_help_targetserver', 1, 0);
    INSERT INTO #sp_table (Name, SignFlag, ComponentFlag) VALUES(N'sp_resync_targetserver', 1, 0);
    INSERT INTO #sp_table (Name, SignFlag, ComponentFlag) VALUES(N'sp_purge_jobhistory', 1, 0);
    INSERT INTO #sp_table (Name, SignFlag, ComponentFlag) VALUES(N'sp_help_jobhistory', 1, 0);
    INSERT INTO #sp_table (Name, SignFlag, ComponentFlag) VALUES(N'sp_add_jobserver', 1, 0);
    INSERT INTO #sp_table (Name, SignFlag, ComponentFlag) VALUES(N'sp_delete_jobserver', 1, 0);
    INSERT INTO #sp_table (Name, SignFlag, ComponentFlag) VALUES(N'sp_help_jobserver', 1, 0);
    INSERT INTO #sp_table (Name, SignFlag, ComponentFlag) VALUES(N'sp_help_downloadlist', 1, 0);
    INSERT INTO #sp_table (Name, SignFlag, ComponentFlag) VALUES(N'sp_enum_sqlagent_subsystems', 1, 0);
    INSERT INTO #sp_table (Name, SignFlag, ComponentFlag) VALUES(N'sp_enum_sqlagent_subsystems_internal', 1, 0);
    INSERT INTO #sp_table (Name, SignFlag, ComponentFlag) VALUES(N'sp_verify_subsystem', 1, 1);
    INSERT INTO #sp_table (Name, SignFlag, ComponentFlag) VALUES(N'sp_verify_subsystems', 1, 0);
    INSERT INTO #sp_table (Name, SignFlag, ComponentFlag) VALUES(N'sp_verify_schedule', 1, 0);
    INSERT INTO #sp_table (Name, SignFlag, ComponentFlag) VALUES(N'sp_add_schedule', 1, 0);
    INSERT INTO #sp_table (Name, SignFlag, ComponentFlag) VALUES(N'sp_attach_schedule', 1, 0);
    INSERT INTO #sp_table (Name, SignFlag, ComponentFlag) VALUES(N'sp_detach_schedule', 1, 0);
    INSERT INTO #sp_table (Name, SignFlag, ComponentFlag) VALUES(N'sp_update_schedule', 1, 0);
    INSERT INTO #sp_table (Name, SignFlag, ComponentFlag) VALUES(N'sp_delete_schedule', 1, 0);
    INSERT INTO #sp_table (Name, SignFlag, ComponentFlag) VALUES(N'sp_get_jobstep_db_username', 1, 0);
    INSERT INTO #sp_table (Name, SignFlag, ComponentFlag) VALUES(N'sp_verify_jobstep', 1, 0);
    INSERT INTO #sp_table (Name, SignFlag, ComponentFlag) VALUES(N'sp_add_jobstep_internal', 1, 0);
    INSERT INTO #sp_table (Name, SignFlag, ComponentFlag) VALUES(N'sp_add_jobstep', 1, 0);
    INSERT INTO #sp_table (Name, SignFlag, ComponentFlag) VALUES(N'sp_update_jobstep', 1, 0);
    INSERT INTO #sp_table (Name, SignFlag, ComponentFlag) VALUES(N'sp_delete_jobstep', 1, 0);
    INSERT INTO #sp_table (Name, SignFlag, ComponentFlag) VALUES(N'sp_help_jobstep', 1, 0);
    INSERT INTO #sp_table (Name, SignFlag, ComponentFlag) VALUES(N'sp_write_sysjobstep_log', 1, 0);
    INSERT INTO #sp_table (Name, SignFlag, ComponentFlag) VALUES(N'sp_help_jobsteplog', 1, 0);
    INSERT INTO #sp_table (Name, SignFlag, ComponentFlag) VALUES(N'sp_delete_jobsteplog', 1, 0);
    INSERT INTO #sp_table (Name, SignFlag, ComponentFlag) VALUES(N'sp_get_schedule_description', 1, 1);
    INSERT INTO #sp_table (Name, SignFlag, ComponentFlag) VALUES(N'sp_add_jobschedule', 1, 0);
    INSERT INTO #sp_table (Name, SignFlag, ComponentFlag) VALUES(N'sp_update_replication_job_parameter', 1, 0);
    INSERT INTO #sp_table (Name, SignFlag, ComponentFlag) VALUES(N'sp_update_jobschedule', 1, 0);
    INSERT INTO #sp_table (Name, SignFlag, ComponentFlag) VALUES(N'sp_delete_jobschedule', 1, 0);
    INSERT INTO #sp_table (Name, SignFlag, ComponentFlag) VALUES(N'sp_help_schedule', 1, 0);
    INSERT INTO #sp_table (Name, SignFlag, ComponentFlag) VALUES(N'sp_help_jobschedule', 1, 0);
    INSERT INTO #sp_table (Name, SignFlag, ComponentFlag) VALUES(N'sp_verify_job', 1, 1);
    INSERT INTO #sp_table (Name, SignFlag, ComponentFlag) VALUES(N'sp_add_job', 1, 0);
    INSERT INTO #sp_table (Name, SignFlag, ComponentFlag) VALUES(N'sp_update_job', 1, 0);
    INSERT INTO #sp_table (Name, SignFlag, ComponentFlag) VALUES(N'sp_delete_job', 1, 0);
    INSERT INTO #sp_table (Name, SignFlag, ComponentFlag) VALUES(N'sp_get_composite_job_info', 1, 1);
    INSERT INTO #sp_table (Name, SignFlag, ComponentFlag) VALUES(N'sp_help_job', 1, 0);
    INSERT INTO #sp_table (Name, SignFlag, ComponentFlag) VALUES(N'sp_help_jobcount ', 1, 0);
    INSERT INTO #sp_table (Name, SignFlag, ComponentFlag) VALUES(N'sp_help_jobs_in_schedule', 1, 0);
    INSERT INTO #sp_table (Name, SignFlag, ComponentFlag) VALUES(N'sp_manage_jobs_by_login', 1, 0);
    INSERT INTO #sp_table (Name, SignFlag, ComponentFlag) VALUES(N'sp_apply_job_to_targets', 1, 0);
    INSERT INTO #sp_table (Name, SignFlag, ComponentFlag) VALUES(N'sp_remove_job_from_targets', 1, 0);
    INSERT INTO #sp_table (Name, SignFlag, ComponentFlag) VALUES(N'sp_get_job_alerts', 1, 0);
    INSERT INTO #sp_table (Name, SignFlag, ComponentFlag) VALUES(N'sp_convert_jobid_to_char', 1, 0);
    INSERT INTO #sp_table (Name, SignFlag, ComponentFlag) VALUES(N'sp_start_job', 1, 0);
    INSERT INTO #sp_table (Name, SignFlag, ComponentFlag) VALUES(N'sp_stop_job', 1, 0);
    INSERT INTO #sp_table (Name, SignFlag, ComponentFlag) VALUES(N'sp_cycle_agent_errorlog', 1, 0);
    INSERT INTO #sp_table (Name, SignFlag, ComponentFlag) VALUES(N'sp_get_chunked_jobstep_params', 1, 0);
    INSERT INTO #sp_table (Name, SignFlag, ComponentFlag) VALUES(N'sp_check_for_owned_jobs', 1, 0);
    INSERT INTO #sp_table (Name, SignFlag, ComponentFlag) VALUES(N'sp_check_for_owned_jobsteps', 1, 0);
    INSERT INTO #sp_table (Name, SignFlag, ComponentFlag) VALUES(N'sp_sqlagent_refresh_job', 1, 0);
    INSERT INTO #sp_table (Name, SignFlag, ComponentFlag) VALUES(N'sp_jobhistory_row_limiter', 1, 1);
    INSERT INTO #sp_table (Name, SignFlag, ComponentFlag) VALUES(N'sp_sqlagent_log_jobhistory', 1, 0);
    INSERT INTO #sp_table (Name, SignFlag, ComponentFlag) VALUES(N'sp_sqlagent_check_msx_version', 1, 0);
    INSERT INTO #sp_table (Name, SignFlag, ComponentFlag) VALUES(N'sp_sqlagent_probe_msx', 1, 0);
    INSERT INTO #sp_table (Name, SignFlag, ComponentFlag) VALUES(N'sp_set_local_time', 1, 1);
    INSERT INTO #sp_table (Name, SignFlag, ComponentFlag) VALUES(N'sp_multi_server_job_summary', 1, 0);
    INSERT INTO #sp_table (Name, SignFlag, ComponentFlag) VALUES(N'sp_target_server_summary', 1, 0);
    INSERT INTO #sp_table (Name, SignFlag, ComponentFlag) VALUES(N'sp_uniquetaskname', 1, 0);
    INSERT INTO #sp_table (Name, SignFlag, ComponentFlag) VALUES(N'sp_addtask', 1, 0);
    INSERT INTO #sp_table (Name, SignFlag, ComponentFlag) VALUES(N'sp_droptask', 1, 0);
    INSERT INTO #sp_table (Name, SignFlag, ComponentFlag) VALUES(N'sp_add_alert_internal', 1, 0);
    INSERT INTO #sp_table (Name, SignFlag, ComponentFlag) VALUES(N'sp_add_alert', 1, 0);
    INSERT INTO #sp_table (Name, SignFlag, ComponentFlag) VALUES(N'sp_delete_alert', 1, 0);
    INSERT INTO #sp_table (Name, SignFlag, ComponentFlag) VALUES(N'sp_help_alert', 1, 0);
    INSERT INTO #sp_table (Name, SignFlag, ComponentFlag) VALUES(N'sp_verify_operator', 1, 0);
    INSERT INTO #sp_table (Name, SignFlag, ComponentFlag) VALUES(N'sp_add_operator', 1, 0);
    INSERT INTO #sp_table (Name, SignFlag, ComponentFlag) VALUES(N'sp_update_operator', 1, 1);
    INSERT INTO #sp_table (Name, SignFlag, ComponentFlag) VALUES(N'sp_help_operator', 1, 0);
    INSERT INTO #sp_table (Name, SignFlag, ComponentFlag) VALUES(N'sp_help_operator_jobs', 1, 0);
    INSERT INTO #sp_table (Name, SignFlag, ComponentFlag) VALUES(N'sp_verify_operator_identifiers', 1, 0);
    INSERT INTO #sp_table (Name, SignFlag, ComponentFlag) VALUES(N'sp_notify_operator', 1, 0);
    INSERT INTO #sp_table (Name, SignFlag, ComponentFlag) VALUES(N'sp_verify_notification', 1, 0);
    INSERT INTO #sp_table (Name, SignFlag, ComponentFlag) VALUES(N'sp_add_notification', 1, 0);
    INSERT INTO #sp_table (Name, SignFlag, ComponentFlag) VALUES(N'sp_update_notification', 1, 0);
    INSERT INTO #sp_table (Name, SignFlag, ComponentFlag) VALUES(N'sp_delete_notification', 1, 0);
    INSERT INTO #sp_table (Name, SignFlag, ComponentFlag) VALUES(N'sp_help_notification', 1, 0);
    INSERT INTO #sp_table (Name, SignFlag, ComponentFlag) VALUES(N'sp_help_jobactivity', 1, 0);
    INSERT INTO #sp_table (Name, SignFlag, ComponentFlag) VALUES(N'sp_enlist_tsx', 1, 1);
    INSERT INTO #sp_table (Name, SignFlag, ComponentFlag) VALUES(N'trig_targetserver_insert', 1, 0);

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

    -- Database Mail: mail host database specific procs
    INSERT INTO #sp_table (Name, SignFlag, ComponentFlag) VALUES(N'sysmail_start_sp', 1, 2)
    INSERT INTO #sp_table (Name, SignFlag, ComponentFlag) VALUES(N'sysmail_stop_sp', 1, 2)
    INSERT INTO #sp_table (Name, SignFlag, ComponentFlag) VALUES(N'sysmail_logmailevent_sp', 1, 0);
    INSERT INTO #sp_table (Name, SignFlag, ComponentFlag) VALUES(N'sp_SendMailMessage', 1, 0);
    INSERT INTO #sp_table (Name, SignFlag, ComponentFlag) VALUES(N'sp_isprohibited', 1, 0);
    INSERT INTO #sp_table (Name, SignFlag, ComponentFlag) VALUES(N'sp_SendMailQueues', 1, 0);
    INSERT INTO #sp_table (Name, SignFlag, ComponentFlag) VALUES(N'sp_ProcessResponse', 1, 0);
    INSERT INTO #sp_table (Name, SignFlag, ComponentFlag) VALUES(N'sp_MailItemResultSets', 1, 0);
    INSERT INTO #sp_table (Name, SignFlag, ComponentFlag) VALUES(N'sp_process_DialogTimer', 1, 0);
    INSERT INTO #sp_table (Name, SignFlag, ComponentFlag) VALUES(N'sp_readrequest', 1, 0);
    INSERT INTO #sp_table (Name, SignFlag, ComponentFlag) VALUES(N'sp_GetAttachmentData', 1, 0);
    INSERT INTO #sp_table (Name, SignFlag, ComponentFlag) VALUES(N'sp_RunMailQuery', 1, 0);
    INSERT INTO #sp_table (Name, SignFlag, ComponentFlag) VALUES(N'sysmail_help_queue_sp', 1, 0);
    INSERT INTO #sp_table (Name, SignFlag, ComponentFlag) VALUES(N'sysmail_help_status_sp', 1, 2)
    INSERT INTO #sp_table (Name, SignFlag, ComponentFlag) VALUES(N'sysmail_delete_mailitems_sp', 1, 0);
    INSERT INTO #sp_table (Name, SignFlag, ComponentFlag) VALUES(N'sysmail_delete_log_sp', 1, 0);
    INSERT INTO #sp_table (Name, SignFlag, ComponentFlag) VALUES(N'sp_send_dbmail', 1, 2)
    INSERT INTO #sp_table (Name, SignFlag, ComponentFlag) VALUES(N'sp_ExternalMailQueueListener', 1, 0);
    INSERT INTO #sp_table (Name, SignFlag, ComponentFlag) VALUES(N'sp_sysmail_activate', 1, 0);
    INSERT INTO #sp_table (Name, SignFlag, ComponentFlag) VALUES(N'sp_get_script', 1, 0);

    -- Maintenance Plans
    INSERT INTO #sp_table (Name, SignFlag, ComponentFlag) VALUES(N'sp_maintplan_delete_log', 1, 0);
    INSERT INTO #sp_table (Name, SignFlag, ComponentFlag) VALUES(N'sp_maintplan_delete_subplan', 1, 0);
    INSERT INTO #sp_table (Name, SignFlag, ComponentFlag) VALUES(N'sp_maintplan_open_logentry', 1, 0);
    INSERT INTO #sp_table (Name, SignFlag, ComponentFlag) VALUES(N'sp_maintplan_close_logentry', 1, 0);
    INSERT INTO #sp_table (Name, SignFlag, ComponentFlag) VALUES(N'sp_maintplan_update_log', 1, 0);
    INSERT INTO #sp_table (Name, SignFlag, ComponentFlag) VALUES(N'sp_maintplan_update_subplan', 1, 0);
    INSERT INTO #sp_table (Name, SignFlag, ComponentFlag) VALUES(N'sp_maintplan_delete_plan', 1, 0);
    INSERT INTO #sp_table (Name, SignFlag, ComponentFlag) VALUES(N'sp_maintplan_start', 1, 0);
    INSERT INTO #sp_table (Name, SignFlag, ComponentFlag) VALUES(N'sp_clear_dbmaintplan_by_db', 1, 0);
    INSERT INTO #sp_table (Name, SignFlag, ComponentFlag) VALUES(N'sp_add_maintenance_plan', 1, 0);
    INSERT INTO #sp_table (Name, SignFlag, ComponentFlag) VALUES(N'sp_delete_maintenance_plan', 1, 0);
    INSERT INTO #sp_table (Name, SignFlag, ComponentFlag) VALUES(N'sp_add_maintenance_plan_db', 1, 0);
    INSERT INTO #sp_table (Name, SignFlag, ComponentFlag) VALUES(N'sp_delete_maintenance_plan_db', 1, 0);
    INSERT INTO #sp_table (Name, SignFlag, ComponentFlag) VALUES(N'sp_add_maintenance_plan_job', 1, 1);
    INSERT INTO #sp_table (Name, SignFlag, ComponentFlag) VALUES(N'sp_delete_maintenance_plan_job', 1, 0);
    INSERT INTO #sp_table (Name, SignFlag, ComponentFlag) VALUES(N'sp_help_maintenance_plan', 1, 0);

    -- Log Shipping
    INSERT INTO #sp_table (Name, SignFlag, ComponentFlag) VALUES(N'sp_add_log_shipping_monitor_jobs', 1, 0);
    INSERT INTO #sp_table (Name, SignFlag, ComponentFlag) VALUES(N'sp_add_log_shipping_primary', 1, 0);
    INSERT INTO #sp_table (Name, SignFlag, ComponentFlag) VALUES(N'sp_add_log_shipping_secondary', 1, 0);
    INSERT INTO #sp_table (Name, SignFlag, ComponentFlag) VALUES(N'sp_delete_log_shipping_monitor_jobs', 1, 0);
    INSERT INTO #sp_table (Name, SignFlag, ComponentFlag) VALUES(N'sp_delete_log_shipping_primary', 1, 0);
    INSERT INTO #sp_table (Name, SignFlag, ComponentFlag) VALUES(N'sp_delete_log_shipping_secondary ', 1, 0);
    INSERT INTO #sp_table (Name, SignFlag, ComponentFlag) VALUES(N'sp_log_shipping_in_sync', 1, 0);
    INSERT INTO #sp_table (Name, SignFlag, ComponentFlag) VALUES(N'sp_log_shipping_get_date_from_file ', 1, 0);
    INSERT INTO #sp_table (Name, SignFlag, ComponentFlag) VALUES(N'sp_get_log_shipping_monitor_info', 1, 0);
    INSERT INTO #sp_table (Name, SignFlag, ComponentFlag) VALUES(N'sp_update_log_shipping_monitor_info', 1, 0);
    INSERT INTO #sp_table (Name, SignFlag, ComponentFlag) VALUES(N'sp_delete_log_shipping_monitor_info', 1, 0);
    INSERT INTO #sp_table (Name, SignFlag, ComponentFlag) VALUES(N'sp_remove_log_shipping_monitor_account', 1, 0);
    INSERT INTO #sp_table (Name, SignFlag, ComponentFlag) VALUES(N'sp_log_shipping_monitor_backup', 1, 0);
    INSERT INTO #sp_table (Name, SignFlag, ComponentFlag) VALUES(N'sp_log_shipping_monitor_restore', 1, 0);
    INSERT INTO #sp_table (Name, SignFlag, ComponentFlag) VALUES(N'sp_change_monitor_role', 1, 0);
    INSERT INTO #sp_table (Name, SignFlag, ComponentFlag) VALUES(N'sp_create_log_shipping_monitor_account', 1, 0);

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

    BEGIN TRANSACTION;

    DECLARE curObjects CURSOR LOCAL FAST_FORWARD
    FOR
    SELECT Name, SignFlag
    FROM #sp_table
    WHERE SignFlag = 1;

    OPEN curObjects;
    FETCH curObjects INTO @SystemProcedureName, @SignFlag;

    WHILE (@@FETCH_STATUS = 0)
    BEGIN
       IF EXISTS(SELECT TOP 1 1 FROM sys.objects WHERE name = @SystemProcedureName)
       BEGIN
          RAISERROR('Adding signature to procedure ''%s''...', 10, 1, @SystemProcedureName) WITH NOWAIT;
          IF (@SignFlag = 1)
          BEGIN
             SET @SQL = N'ADD SIGNATURE TO ' + QUOTENAME(@SystemProcedureName) + N' BY CERTIFICATE [##MS_AgentSigningCertificate##] WITH PASSWORD = ''Yukon90_'''
             EXECUTE(@SQL)
             IF (@@ERROR != 0) RAISERROR('Unable to sign stored procedure ''%s''.', 15, 1, @SystemProcedureName) WITH NOWAIT;
          END
        END

        FETCH curObjects INTO @SystemProcedureName, @SignFlag;
    END

    CLOSE curObjects;
    DEALLOCATE curObjects;

    COMMIT TRANSACTION;

    DROP TABLE #sp_table;

    /* Drop certificate private key */
    ALTER CERTIFICATE [##MS_AgentSigningCertificate##] REMOVE PRIVATE KEY;
    IF (@@ERROR != 0) RAISERROR('Unable to alter certificate ##MS_AgentSigningCertificate## in msdb.', 15, 1) WITH NOWAIT;

    /* Export certificate to master */
    DECLARE @CertificateName NVARCHAR(520)

    SELECT TOP 1 @CertificateName =
        SUBSTRING(physical_name, 1, LEN(physical_name) - CHARINDEX('\', REVERSE(physical_name)) + 1)
        + CONVERT(NVARCHAR(36), NEWID()) + N'.cer'
    FROM sys.master_files
    WHERE database_id = DB_ID('msdb')
    AND type_desc = 'ROWS';
  
    EXECUTE(N'DUMP CERTIFICATE [##MS_AgentSigningCertificate##] TO FILE = ''' + @CertificateName + '''')
    IF (@@ERROR != 0) RAISERROR('Unable to dump ##MS_AgentSigningCertificate## from msdb.', 15, 1) WITH NOWAIT;

    /* Change context to master for the creation of the certificate. */
    USE [master];

    IF EXISTS (SELECT TOP 1 1 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 = ''' + @CertificateName + ''';')
    IF (@@ERROR != 0) RAISERROR('Unable to create ##MS_AgentSigningCertificate## certificate in master.', 15, 1) WITH NOWAIT;

    /* Create login */
    CREATE LOGIN [##MS_AgentSigningCertificate##] FROM CERTIFICATE [##MS_AgentSigningCertificate##];
    IF (@@ERROR != 0) RAISERROR('Unable to create ##MS_AgentSigningCertificate## login.', 15, 1) WITH NOWAIT;

    /* Create certificate based user for execution granting */
    CREATE USER [##MS_AgentSigningCertificate##] FOR CERTIFICATE [##MS_AgentSigningCertificate##];
    IF (@@ERROR != 0) RAISERROR('Unable to create ##MS_AgentSigningCertificate## user.', 15, 1) WITH NOWAIT;

    /* enable certificate for OBD */
    EXEC sys.sp_SetOBDCertificate N'##MS_AgentSigningCertificate##', N'ON'

    GRANT EXECUTE TO [##MS_AgentSigningCertificate##];

END TRY
BEGIN CATCH
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION;

    SET @ErrorSeverity = ERROR_SEVERITY();
    SET @ErrorState = ERROR_STATE();
    SET @StackTrace =
        'Msg ' + CAST(ERROR_NUMBER() AS VARCHAR)
        + ', Level ' + CAST(ERROR_SEVERITY() AS VARCHAR)
        + ', State ' + CAST(ERROR_STATE() AS VARCHAR)
        + ', Line ' + CAST(ERROR_LINE() AS VARCHAR)
        + ', Procedure: ' + ISNULL(ERROR_PROCEDURE(), 'N/A')
        + ', Caller: ' + ISNULL(@ProcedureName, 'N/A')
        + '.' + CHAR(13) + CHAR(10) + ERROR_MESSAGE();
    RAISERROR(@StackTrace, @ErrorSeverity, @ErrorState);
END CATCH

Post #799449

Tony Fountain

Posted Wednesday, October 07, 2009 1:01 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, October 21, 2009 9:31 AM
Points: 146, Visits: 125

After running the script and rerunning the previously posted tests, everything appears to function.

Post #799458

Tony Fountain

Posted Friday, October 09, 2009 1:40 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, October 21, 2009 9:31 AM
Points: 146, Visits: 125

Ok, for what it's worth... I found out I can simply rerun the latest service pack exe and it will also correct this problem... go figure - so much simpler!

Post #801062

Elliott Whitlow

Posted Friday, October 09, 2009 3:41 PM

SSCertifiable

Group: General Forum Members
Last Login: Wednesday, April 24, 2013 12:02 PM
Points: 5,854, Visits: 4,873

Ouch...

CEWII

Post #801116

ddg8885

Posted Wednesday, October 20, 2010 10:21 AM

Forum Newbie

Group: General Forum Members
Last Login: Monday, March 26, 2012 2:24 PM
Points: 3, Visits: 148

I searched the error for a while and saw a lot of things about certificates differing between msdb and master. But I checked the certificates and they were identical.

I compared my server to others, and noticed that the ##MS_AgentSigningCertificate## user was missing from the master database. Running the following query worked for me:

USE MASTER
CREATE USER [##MS_AgentSigningCertificate##] FOR LOGIN [##MS_AgentSigningCertificate##]
GO

GRANT EXECUTE TO [##MS_AgentSigningCertificate##]
go

Post #1007865

opc.three

Posted Wednesday, March 06, 2013 3:42 PM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 7:46 AM
Points: 6,696, Visits: 11,718

ddg8885 (10/20/2010)

I know this is old but wanted to thank you for posting a solution. I think I had a few things going on. Here is what I did:

1. (forgot to mention) First I followed the steps in KB article http://support.microsoft.com/kb/2000274
2. Added ##MS_AgentSigningCertificate## to master and granted EXEC
3. I also had a disabled guest User account in msdb so had to also issue this:

USE msdb;
GRANT CONNECT TO guest;

After that database listings and Agent Job listings started working in Object Explorer.

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy

Post #1427674

clare.xia

Posted Monday, May 06, 2013 4:59 PM

Valued Member

Group: General Forum Members
Last Login: Thursday, May 09, 2013 2:46 PM
Points: 55, Visits: 236

Tony Fountain (10/9/2009)

Ok, for what it's worth... I found out I can simply rerun the latest service pack exe and it will also correct this problem... go figure - so much simpler!

Tony,

How did you rerun the service pack? Don't you get "no update" error?

Post #1449935

« Prev Topic | Next Topic »

17 posts, Page 2 of 2

««1 2

Permissions