May 4, 2007 at 8:16 am
Hi All,
I am trying to manage permissions for a user to see the status of jobs, run jobs and manage jobs. I also need the user to be able to run Xp_cmdshell.
I want to do this without granting SA rights, is this possible ? as I had a problem with SQL 2005 SP 1, only job owners can alter jobs, and only SA users can use xp_cmdshell.
I found this website from Microsoft, but the DENY permissions don’t seem to work.
http://msdn2.microsoft.com/en-us/library/ms177518.aspx
May 4, 2007 at 8:37 am
John,
for xp_cmdshell you can grant the users execution permissions on the procedure.
About the jobs it's a bit more difficult. Viewing the status, starting and stopping of jobs can be done by adding the user to the SQLAgentOperators role in the msdb. But even then the user can only edit jobs he owns.
For more info see: http://msdn2.microsoft.com/en-us/library/ms188283.aspx
Markus
[font="Verdana"]Markus Bohse[/font]
May 4, 2007 at 8:59 am
Another easy way to do this is build a query to get the status and insert this data into a table in another db every minute. That way you don't even have to grant rights to msdb. Just let the user query the table in some other db.
May 4, 2007 at 9:00 am
Hi
I already had a look at the website, and as you rightly said users can only edit/manage jobs they own, the problem is that in the environment that I work in, there are various users who need to be able to manage jobs instead of one person, and the job owner parameter can only be one login/user.
What I am thinking of doing is giving users Sysadmin rights, but removing the ability to do certain things, eg alter logins or change database settings, so that the privilege will not be abused.
The problem is that these commands don’t just work:
See: http://msdn2.microsoft.com/en-us/library/ms177518.aspx
Thanks
May 5, 2007 at 8:06 am
John,
you can't deny permission for the sysadmin role. And since you can't create your own server role either, the only solution I ca think of is using a generic login which owns all jobs. Your users can then use this account to mange the jobs.
Markus
[font="Verdana"]Markus Bohse[/font]
May 7, 2007 at 7:42 am
May 7, 2007 at 8:25 am
No SP2 doesn't change any of this, mainly because from MS point of view it is not a problem. And from a security point of view I have to agree with them.
The best workaround to my knowledge is using an external scheduling applications like Opalis which starts osql scripts (or better sqlcmd in 2005). This way you're not depending on the SQL server security concepts.
Markus
[font="Verdana"]Markus Bohse[/font]
May 13, 2007 at 3:16 pm
May 14, 2007 at 8:18 am
If you're willing to let them run xp_cmdshell, but not just drop them in sysadmins?
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply