How to execute msdb.dbo.sp_start_job from a stored procedure in user database in sql server 2005
- by Ram
Hi Everyone,
I am trying to execute a msdb.dbo.sp_start_Job from MyDB.dbo.MyStoredProc in order to execute MyJob
1) I Know that if i give the user a SqlAgentUser role he will be able to run the jobs that he owns (BUT THIS IS WHAT I OBSERVED : THE USER WAS ABLE TO START/STOP/RESTART THE SQL AGENT SO I DO NOT WANT TO GO THIS ROUTE)
- Let me know if i am wrong , but i do not understand why would such a under privileged user be able to start/stop agents .
2)I know that if i give execute permissions on executing user to msdb.dbo.Sp_Start_job and
Enable Ownership chaining or enable Trustworthy on the user database it would work
(BUT I DO NOT WANT TO ENABLE OWNERSHIP CHAINING NOR TRUSTWORTHY ON THE USER DATABASE)
3)I this this can be done by code signing
User Database
i)create a stored proc MyDB.dbo.MyStoredProc
ii)Create a certificae job_exec
iii)sign MyDB.dbo.MyStoredProc with certificate job_exec
iv)export certificate
msdb
i)Import Certificate
ii)create a derived user from this certificate
iii)grant authenticate for this derived user
iv)grant execute on msdb.dbo.sp_start_job to the derived user
v)grant execute on msdb.dbo.sp_start_job to the user executing the MyDB.dbo.MyStoredProc
but i tried it and it did not work for me
-i dont know which piece i am missing or doing wrong
so please provide me with a simple example (with scripts) for executing msdb.dbo.sp_start_job from user stored prod MyDB.dbo.MyStoredProc using code signing
Many Many Many Thanks in Advance
Thanks
Ram
