4

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

Improve this question

3 Answers 3

Reset to default
1

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?!?!?!

Improve this answer
1
  • I think Nick is correct. It's is the solution that I've used once already. I would definitely suggest doing a Run As on SSMS using the login that you've configured as the job owner. I suspect it won't be able to stop SQL Server Agent.
    – Ra Osolage
    Jun 7, 2011 at 2:55
0

Code signing is the proper way to do this. The only thing I see missing from your description is that MyDB.dbo.MyStoredProc must have an EXECUTE AS clause (even if is EXECUTE AS CALLER), otherwise the code signing is not taking effect.

Improve this answer
0

Jonathan Kehayias has published a solution that seems a perfect solution for you.

Take a look at this to see if this is a good fit for you. http://www.sqlservercentral.com/articles/Security/68873/

Improve this answer

You must log in to answer this question.