January 15, 2008 at 7:49 am
I have 1 power user and 1 DBA "backup" user that need to be able to administer jobs, run sp_who2, kill active spids and probably some other activities on our production box. The problem is that we are possibly going to be bringing our payroll databases onto this box. I need to have everyone locked out of the payroll databases, but still allow these 2 users to perform some administrative tasks. The power user needs to troubleshoot problems with one of our production databases, such as blocked processes, or jobs that failed.
Currently, to allow them to work with jobs, I created an A/D group called SQLAgentOperator that has AgentOperatorRole permissions in MSDB, and put them in that group. They are also in other groups specific to various database permissions
Any thoughts of a better way to address what I'm trying to do ?
TIA.
January 15, 2008 at 8:12 am
just a thought ...
You figured out sqlagent's roles .. that will be OK.
Maybe the logon-triggers can help you out ( need to be on sp2 ) so you can kick them out if needed.
You can grant VIEW SERVER STATE to run sp_who2.
To be able to kill a spid, it membership in the sysadmin or processadmin fixed roles. (Choose the least privileged)
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
January 15, 2008 at 8:32 am
Johan has some good ideas. I think you need to go with the custom role that you have, work out the permissions.
And write us an article! I'm sure other people have similar issues and it would be good to see in print how you've done it.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply