SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
Tony Fountain
Tony Fountain
SSC-Addicted
SSC-Addicted (455 reputation)SSC-Addicted (455 reputation)SSC-Addicted (455 reputation)SSC-Addicted (455 reputation)SSC-Addicted (455 reputation)SSC-Addicted (455 reputation)SSC-Addicted (455 reputation)SSC-Addicted (455 reputation)

Group: General Forum Members
Points: 455 Visits: 202
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


Tony Fountain
Tony Fountain
SSC-Addicted
SSC-Addicted (455 reputation)SSC-Addicted (455 reputation)SSC-Addicted (455 reputation)SSC-Addicted (455 reputation)SSC-Addicted (455 reputation)SSC-Addicted (455 reputation)SSC-Addicted (455 reputation)SSC-Addicted (455 reputation)

Group: General Forum Members
Points: 455 Visits: 202
After running the script and rerunning the previously posted tests, everything appears to function.
Tony Fountain
Tony Fountain
SSC-Addicted
SSC-Addicted (455 reputation)SSC-Addicted (455 reputation)SSC-Addicted (455 reputation)SSC-Addicted (455 reputation)SSC-Addicted (455 reputation)SSC-Addicted (455 reputation)SSC-Addicted (455 reputation)SSC-Addicted (455 reputation)

Group: General Forum Members
Points: 455 Visits: 202
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!
Elliott Whitlow
Elliott Whitlow
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25118 Visits: 5314
Ouch...

CEWII
ddg8885
ddg8885
Valued Member
Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)

Group: General Forum Members
Points: 55 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
Orlando Colamatteo
Orlando Colamatteo
SSC-Forever
SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)

Group: General Forum Members
Points: 41772 Visits: 14413
ddg8885 (10/20/2010)
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

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
clare.xia
clare.xia
Say Hey Kid
Say Hey Kid (698 reputation)Say Hey Kid (698 reputation)Say Hey Kid (698 reputation)Say Hey Kid (698 reputation)Say Hey Kid (698 reputation)Say Hey Kid (698 reputation)Say Hey Kid (698 reputation)Say Hey Kid (698 reputation)

Group: General Forum Members
Points: 698 Visits: 250
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?
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search