Trigger + sp_start_job = Execute permissions Denied...

Well I had a novel idea of creating a trigger on a table to kick off a SQL job, asynchronously. Seemed simple enough. And it was until I needed to have the update on the table be executed under the applications security context. Bah. I could run cause I part of sysadmin. Google, google, google and then came across this post at StackOverflow.com. Led me to add applications user to

* SQLAgentUserRole
* SQLAgentReaderRole
* SQLAgentOperatorRole
in msdb... BUT when selecting these roles I inadvertently added TargetServersRole :-(. All four appeared at end of possible role to select, sniff sniff.

After chasing my tail and triple checking the apps user, profiling the DB, and various poor mans debugging, I went back to google and found this GEM @ serverfault.com:

select dp.NAME AS principal_name,
dp
.type_desc AS principal_type_desc,
o
.NAME AS object_name,
p
.permission_name,
p
.state_desc AS permission_state_desc
from sys.database_permissions p
left OUTER JOIN sys.all_objects o on p.major_id = o.OBJECT_ID
inner JOIN sys.database_principals dp on p.grantee_principal_id = dp.principal_id
where o.name = 'sp_start_job'
ARggggghhh, shoot me!

principal_nameprincipal_type_descobject_namepermission_namepermission_state_desc
TargetServersRoleDATABASE_ROLEsp_start_jobEXECUTEDENY

Removed that role and YAY!!!.

All that was really needed was SQLAgentOperatorRole.

On another note, I discovered TRY CATCH are useless in a trigger, or at least they were for me. Any errors in a trigger result in a rollback. I wanted at least part of the trigger to execute and the second part in question to fail gracefully.

Live and learn.

~Enjoy

Comments :

0 comments to “Trigger + sp_start_job = Execute permissions Denied...”