Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

The EXECUTE permission was denied on the object 'xp_sqlagent_enum_jobs', database 'mssqlsystemresource', schema 'sys'. Expand / Collapse
Author
Message
Posted Wednesday, October 7, 2009 12:52 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Sunday, September 14, 2014 11:55 AM
Points: 146, Visits: 136
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
Posted Wednesday, October 7, 2009 1:01 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Sunday, September 14, 2014 11:55 AM
Points: 146, Visits: 136
After running the script and rerunning the previously posted tests, everything appears to function.
Post #799458
Posted Friday, October 9, 2009 1:40 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Sunday, September 14, 2014 11:55 AM
Points: 146, Visits: 136
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
Posted Friday, October 9, 2009 3:41 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, May 15, 2014 5:11 PM
Points: 6,067, Visits: 5,283
Ouch...

CEWII
Post #801116
Posted Wednesday, October 20, 2010 10:21 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Wednesday, March 6, 2013 3:42 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 6:43 AM
Points: 7,127, Visits: 12,656
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
Post #1427674
Posted Monday, May 6, 2013 4:59 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, October 22, 2013 11:43 AM
Points: 56, Visits: 249
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 »

Add to briefcase ««12

Permissions Expand / Collapse