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
Comments :
Post a Comment