WITH EXECUTE AS Question
-
I was thinking that I could create a sp that executed sp_start_job under a different account from the account which is calling the sp, however the following fails with permission errors on sp_start_job even thou Administrator has access.
I guess sp_start_job doesnt like the context being Impersonated !!!
CREATE
PROCEDURE [dbo].[sp_testme]
WITH EXECUTE AS 'Administrator'
AS
exec
msdb...sp_start_job @job_name = N'Some Job Name'
-
Firstly the Administrator login doesn't need to be a sysadmin login.
The job should be owned by 'Administrator';
The user database you have created the sp in should have TRUSTWORTHY set to ON;
Administrator login should be user of msdb and added to the SQLAgentUserRole of msdb; also should be user of your db (that one with the sp sp_testme created)
You've got too many dots (sintax below)
alter
PROCEDURE [dbo].[sp_testme]
WITH
EXECUTE AS 'administrator'
AS
EXEC
msdb..sp_start_job SomeJobName
GO
-
Its probably the TRUSTWORTH set to ON, never seen that one before (the account already belongs to SQLAgentUserRole)!!!
-
You have a detailed explanation as to why you need to turn TRUSTWORTHY ON in the BOL.
-
yeah as I discovered. Its working fine now.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply