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_name	principal_type_desc	object_name	permission_name	permission_state_desc
TargetServersRole	DATABASE_ROLE	sp_start_job	EXECUTE	DENY

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