January 30, 2011 at 5:58 am
Hi
I have a job to kill all the sleeping connections to my server, now i want to give access to this job to one of my user how can i give it to him.
Does i have to give him admin privlages to him to run this job.
i want to give minimum privillages to the user.
Is this possible if yes how?
NEVER NEVER EVER GIVE UP;-)
January 30, 2011 at 6:35 am
Kill requires "Alter any connection" permission. Grant this to the user. But i would think twice about doing this on a prod system.
January 30, 2011 at 5:03 pm
scorpianprince (1/30/2011)
HiI have a job to kill all the sleeping connections to my server, now i want to give access to this job to one of my user how can i give it to him.
How was already answered... but I'm curious as hell.. WHY?! And never try to kill any spid at 50 or less... those are system spids.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
January 30, 2011 at 9:08 pm
as for as i know sql server 2005, you'll need put the user in one of these roles. every time i deal with these roles, i have to re-read what is what so i do the right thing.... you can read about these roles too.
SQLAgentOperatorRole
SQLAgentUserRole
SQLAgentReaderRole
the only trouble here is that when the user is in one of these rules, in most cases the role allows them to do the same thing to every job.
i think in this case you would need to create a duplicate job (with different name), this user being the owner, then giving the user SQLAgentOperatorRole role.
****************
the only way other than the above would be a hack. create a table, give this one user write access to it, give user insert sql to insert certain value in one column then have a job that runs under your id run every so many minutes where first step looks in this table for certain value, if value is there, the job runs to step 2 to kill sleeping processes, if value not there, force job to cancel with 'exec msdb..sp_stop_job 'jobname'
step 3 of the job would clean up the table (remove the record this user put in there).
only trouble with this hack is that once you start doing crazy workarounds for just 1 user, where is it going to end? end up with a system with a lot of junk on it.
January 31, 2011 at 5:34 am
I find myself asking the same question as Craig. Why would you want to do this? What problem are you having that you needed to develop this job?
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply