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
Best Answer
1) I think SSMS is doing something that may be considered a 'feature'. What your observing is that SSMS allows you to restart SQL Server Agent with your windows permissions, no matter what permissions you have in SQL Server, or what permissions you've logged into the server in Object Explorer eg. i created a sql account with only the SqlAgentUser role in msdb & was able to restart the Agent, although I started SSMS as a local administrator.
I tried to do the same thing again, although I created a local windows user account & started SSMS using Run As.. as that local non-admin user. What i found was that I couldn't stop the agent , in fact, SSMS crashed when i tried (oh dear!) but the agent kept running. ...So the moral of the story is, make sure users who you only want to be able to run a specific jobs are not local admin on your SQL Server box. If they are, then they can already restart the agent, and SQL Server for that matter! so quit your stressing!!
2) You can do something like create your own sproc that calls
msdb.dbo.sp_start_job@yourjobid and use EXECUTE AS or setuser inside that sproc. Then GRANT your user permission to only that sproc.3) Are you insane?!?!?!