WITH EXECUTE AS Question

Question

Post reply

WITH EXECUTE AS Question

Ron Pitts

Valued Member

Points: 57
More actions
September 3, 2007 at 7:20 pm

#180623

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'

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

michaela SSCertifiable Points: 5649 More actions · Answer 1

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