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 Eights!
SSC Eights! (855 reputation)SSC Eights! (855 reputation)SSC Eights! (855 reputation)SSC Eights! (855 reputation)SSC Eights! (855 reputation)SSC Eights! (855 reputation)SSC Eights! (855 reputation)SSC Eights! (855 reputation)

Group: General Forum Members
Points: 855 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 Eights!
SSC Eights! (855 reputation)SSC Eights! (855 reputation)SSC Eights! (855 reputation)SSC Eights! (855 reputation)SSC Eights! (855 reputation)SSC Eights! (855 reputation)SSC Eights! (855 reputation)SSC Eights! (855 reputation)

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

Group: General Forum Members
Points: 855 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
SSC Guru
SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)

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

CEWII
ddg8885
ddg8885
SSC-Enthusiastic
SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)

Group: General Forum Members
Points: 139 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 Guru
SSC Guru (104K reputation)SSC Guru (104K reputation)SSC Guru (104K reputation)SSC Guru (104K reputation)SSC Guru (104K reputation)SSC Guru (104K reputation)SSC Guru (104K reputation)SSC Guru (104K reputation)

Group: General Forum Members
Points: 104116 Visits: 14506
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
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1664 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?
lamprecht
lamprecht
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 2
--https://support.microsoft.com/de-de/help/2000274/how-to-fix-permission-issues-when-moving-msdb-database-between-different-instances
--
use msdb
go
-- Backup the Agent certificate from the remote server to a file
BACKUP CERTIFICATE [##MS_AgentSigningCertificate##] TO FILE = 'd:\ServerInstall\MS_AgentSigningCertificate.remote_server.cer'
go
use master
go
-- re-create the agent certificate on master
-- Note: Because we are making these changes using a regular user and not as part of setup, the name
-- cannot include the ## token.
-- Creating a regular certificate in this case should be the equivalent as we only need it to derive a SID

CREATE CERTIFICATE [MS_AgentSigningCertificate.remote_server] FROM FILE = 'd:\Serverinstall\MS_AgentSigningCertificate.remote_server.cer'
go
-- Recreate the user mapped to the cert and grant the same permissions that the regular certificate needs.
CREATE USER [MS_AgentSigningCertificate.remote_server] FROM CERTIFICATE [MS_AgentSigningCertificate.remote_server]
go
GRANT EXECUTE TO [MS_AgentSigningCertificate.remote_server]
go
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