Execute As not impersonating permissions when trying to create sql server agent jobs programmatically

0
votes

I am trying to create a stored procedure that creates a job and step programmatically and runs it. This works, but I also need to impersonate a user as the end-user will not have permission to do this.

I have created a test user (sa2) and given it permissions and I can run my procedure fine. If I add the line WOTJ EXECUTE AS 'sa2' (or owner) and run the same procedure while still logged in as sa2 I get the following errors:

Msg 229, Level 14, State 5, Procedure msdb..sp_add_job, Line 1 [Batch Start Line 2] The EXECUTE permission was denied on the object 'sp_add_job', database 'msdb', schema 'dbo'. Msg 229, Level 14, State 5, Procedure msdb..sp_add_jobstep, Line 1 [Batch Start Line 2] The EXECUTE permission was denied on the object 'sp_add_jobstep', database 'msdb', schema 'dbo'. Msg 229, Level 14, State 5, Procedure msdb..sp_add_jobserver, Line 1 [Batch Start Line 2] The EXECUTE permission was denied on the object 'sp_add_jobserver', database 'msdb', schema 'dbo'. Msg 229, Level 14, State 5, Procedure msdb..sp_start_job, Line 1 [Batch Start Line 2] The EXECUTE permission was denied on the object 'sp_start_job', database 'msdb', schema 'dbo'.

My ultimate goal is to get this working for another user, but it seems strange that I can' do this logged in as sa2 with the EXECUTE AS line included in the procedure declaration.

Does anybody know why this is, and what I can do to get round it?

1
sql-serverpermissionsimpersonationsql-server-agentexecute-as
Read: docs.microsoft.com/en-us/previous-versions/sql/… and sommarskog.se/grantperm.html – David Browne - Microsoft
You need to add sa2 to msdb and grant those required permissions – PeterHe
You could either set your database to trustworthy on or sign the procedure with a certificate or asymmetric key. The "impersonated" user in your db must have a login and the login must have sql agent job permissions either inherited via the login's server role(s) or explicitly in sqlagentrole permissions in msdb (the login needs a msdb dbuser for the latter) – lptr

1 Answers

0
votes

Thanks for the help. The answer was in the second post by david browne The second solution (6.3) helped