September 23, 2009 at 11:46 am
Problem Statement:
I have some users that are not a part of any server role (except the default public). For each of the user databases their permissions are tuned to deal only with user objects (tables, procedures, views, etc - standard read, write, execute, etc). We have no issue with their permissions as far as it relates to any of the user databases. However they also need the ability to administer SQL Agent jobs so I added them as users (USE [msdb]; CREATE USER <login> FOR LOGIN <login>;) and then added them to the SQLAgentOperatorRole.
Now when these users attempt to expand the SQL Server Agent --> Jobs node in SSMS, they receive the error The EXECUTE permission was denied on the object 'xp_sqlagent_enum_jobs', database 'mssqlsystemresource', schema 'sys'..
I have Googled and Binged and found two possible solutions - (a) follow the KB article http://support.microsoft.com/kb/2000274 and (b) add the users to the master database.
This raises two situations for me. First, I do not fully follow the KB article since not all the instances this problem exists are due to restored databases. Second, I do not like the idea of giving my users access to the master database. I did do a test and added the users to the master database and gave them db_owner permissions. After doing this, the error went away.
My question is, does anyone else have experience with this error and what solution do you prefer (one of the above or even a different one).
September 30, 2009 at 6:53 am
No opinions?
September 30, 2009 at 7:08 am
hmm interesting,
I had a sql login called test, added it as a user to msdb, added it to the sqlagentoperator role and it can view the jobs and edit the jobs just fine.
what is the build version of your sql instance?
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
October 1, 2009 at 3:15 pm
We have seven servers total but these users in question only have access to 3 of the servers:
DEV - Version: 9.00.3042.00, SP2, Developer Edition
QA - Version: 9.00.3042.00, SP2, Enterprise Edition
Staging - Version: 9.00.3042.00, SP2, Enterprise Edition
Note all are 32 bit.
What version are you on and can you identify the permissions your test user has for all databases? I have two scripts that can help you with that (see attached). One is for server permissions, the other is for individual database permissions. This way if SQL Server is doing something under the hood this should uncover that and I can compare the permissions to my users.
I personally wonder if it is tied the the certificate. I inherited the databases and it appears some of the ## system accounts have been dropped.
October 6, 2009 at 2:30 pm
Ok, I have an update and a good idea what the issue is. Since our base issue is users that are part of the SQLAgentOperatorRole are unable to view/edit SQL Agent jobs (as the original error or a form of it is generated), I created the following script to test this out on several servers (7 in total). 3 of the servers generate the error, 4 did not.
SET NOCOUNT ON;
GO
/* Fully remove the test server principal if it exists */
IF EXISTS (SELECT TOP 1 1 FROM [sys].[server_principals] WHERE name = 'SQLAgentTest')
BEGIN
USE [master]; DROP LOGIN [SQLAgentTest];
END
/* Fully remove the test database principal if it exists */
IF EXISTS (SELECT TOP 1 1 FROM [msdb].[sys].[database_principals] WHERE name = 'SQLAgentTest')
BEGIN
USE [msdb]; DROP USER [SQLAgentTest];
END
GO
/* Create base account with base permissions */
USE [master]; CREATE LOGIN [SQLAgentTest] WITH PASSWORD = 'someRandomValue', CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF;
USE [msdb]; CREATE USER [SQLAgentTest] FOR LOGIN [SQLAgentTest];
USE [msdb]; EXEC sp_addrolemember @rolename='SQLAgentOperatorRole', @membername='SQLAgentTest';
GO
/* Now test and see if the error is raised */
USE [msdb]; EXECUTE AS LOGIN='SQLAgentTest';
GO
USE [msdb]; EXEC [msdb].[dbo].[sp_help_job];
GO
USE [msdb]; REVERT;
GO
/* Remove the principals since testing was completed */
USE [master]; DROP LOGIN [SQLAgentTest];
USE [msdb]; DROP USER [SQLAgentTest];
GO
The error that is generated is close to something like
Msg 229, Level 14, State 5, Procedure xp_sqlagent_enum_jobs, Line 1
The EXECUTE permission was denied on the object 'xp_sqlagent_enum_jobs', database 'mssqlsystemresource', schema 'sys'.
The exact error depends on the action the user is performing and the only thing that changes is the object name in the error.
Now, I'm comparing the ##MS_AgentSigningCertificate## certificate in master and msdb using this query:
SELECT 'master', *
FROM master.sys.certificates
WHERE name = '##MS_AgentSigningCertificate##'
UNION
SELECT 'msdb', *
FROM msdb.sys.certificates
WHERE name = '##MS_AgentSigningCertificate##'
ORDER BY 1;
On the 4 servers that generate an error, these certificates are not the same. On the 3 servers that do work, these certificates are the same. I'm guessing SQL Server uses this internally to apply the necessary permissions. My question is, how do I fix these certificates? Thoughts?
October 6, 2009 at 3:12 pm
Do any of the ones that fail happen to be restored from somewhere else? Are you having any other securiy problems on those databases? Can you setup proxy users? I'm guessing you can't setup proxy users because it can't encrypt the credentials..
CEWII
October 6, 2009 at 3:14 pm
Also, in reference to that KB article, you might just want to do it anyway..
CEWII
October 7, 2009 at 7:11 am
Elliott W (10/6/2009)
Do any of the ones that fail happen to be restored from somewhere else? Are you having any other securiy problems on those databases? Can you setup proxy users? I'm guessing you can't setup proxy users because it can't encrypt the credentials..CEWII
This is the only known issue at the moment. And we are a development shop so we tend to be exposed to more of the finer issues of SQL from time to time. As far as just doing the steps outlined in the KB article, I'm hoping it will recreate the same certificate but from the looks of it, it might actually create a new certificate. Are you reading that the same? It appears the code to actually create the certificate is in installation SQL scripts. I've thought about just dropping and recreating both of them using that code (it contains the password to use).
October 7, 2009 at 7:59 am
I got the impression that it recreated it. One thing I've been thinking about it your server master key, you might be able to regen it and get them in sync, but I'm not sure.
I also believe that when those keys are regenerated and the existing box didn't have a valid decryption key there is some loss, such as you can no longer decrypt existing SQL passwords and such.. I am basing this one when I restored a master to a new box I couldn't access any of the linked servers because the credentials could not be decrypted because the server master key didn't match, when I restored that I was back in business.
There are others who probably know more about this particular item than I..
CEWII
October 7, 2009 at 9:38 am
Ok, I've been reviewing the installation scripts and I think I know what needs to be done. Here is my summary of steps:
- Create certificate ##MS_AgentSigningCertificate## in msdb
- Add the signature of the certificate to specific system procedures (too many to list atm)
- Dump the certificate ##MS_AgentSigningCertificate## in msdb to file
- Create certificate ##MS_AgentSigningCertificate## in master from the file
- Create server login ##MS_AgentSigningCertificate## from the certificate in master
- Create database user ##MS_AgentSigningCertificate## for the certificate in master
- Enable the certificate for ODB (not sure what this is yet but it's in the script :))
- Grant execute to ##MS_AgentSigningCertificate##
Obviously these steps assume no objects already exist so logic will need to be added to account for these conditions. But with this direction, does anyone see any problems with a script that performs all these actions? Any steps that might be overlooked? I'll post a sample script later.
October 7, 2009 at 12:52 pm
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
October 7, 2009 at 1:01 pm
After running the script and rerunning the previously posted tests, everything appears to function.
October 9, 2009 at 1:40 pm
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!
October 9, 2009 at 3:41 pm
Ouch...
CEWII
October 20, 2010 at 10:21 am
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
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply