March 13, 2014 at 6:45 am
Hi, I've been battling with this for some time now and can't see what I'm missing.
I have a windows user who is the dbo for a database.
I'm trying to create a SQL Agent Job with that user as the owner.
They are a member of the SQLAgentUserRole
If I run the following SQL I can create the Job
declare @Owner varchar(100)
declare @JobID uniqueidentifier
EXEC msdb.dbo.sp_add_job @job_name = 'TestJob', @enabled = 1,
@notify_level_eventlog = 2, -- When the job fails
@Owner_login_name = @Owner,
@job_id = @JobId OUTPUT
select @JobID
If however I put that code in a sored procedure to execute as the owner and run it
alter procedure test with Execute as Owner
as
declare @Owner varchar(100)
declare @JobID uniqueidentifier
EXEC msdb.dbo.sp_add_job @job_name = 'TestJobstoredProc', @enabled = 1,
@notify_level_eventlog = 2, -- When the job fails
@Owner_login_name = @Owner,
@job_id = @JobId OUTPUT
select @JobID
go
execute test
I get an error
Msg 229, Level 14, State 5, Procedure sp_add_job, Line 1
The EXECUTE permission was denied on the object 'sp_add_job', database 'msdb', schema 'dbo'.
What am I missing?
Please help
March 13, 2014 at 9:12 am
OK worked it out, though not sure of exact reasoning behind it.
I found this statement in MS help for the Execute As User statement
While the context switch to the database user is active, any attempt to access resources outside of the database will cause the statement to fail. This includes USE database statements, distributed queries, and queries that reference another database that uses three- or four-part identifiers.
So removing the 'with execute as owner' from the stored procedure made it work
alter procedure test --with Execute as Owner
as
declare @Owner varchar(100)
declare @JobID uniqueidentifier
EXEC msdb.dbo.sp_add_job @job_name = 'TestJobstoredProc', @enabled = 1,
@notify_level_eventlog = 2, -- When the job fails
@Owner_login_name = @Owner,
@job_id = @JobId OUTPUT
select @JobID
go
execute test
go
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply