Post reply

Limit connections to server/database by user

  • August 30, 2007 at 9:18 am

    #180498

    Anyone know of a way to limit the number of connections to a server/database for a particular user without altering @@Max_Connections. I only want to stop one individual from running so many queries at once as he is negatively impacting server and SAN performance for the rest of the users and will not stop, even though he's been asked many time not to do so. We have no control over his actions, so I want to throttle him down. He's using Windows Authentication on SQL 2000. Thanks in advance if you do.

  • August 30, 2007 at 11:56 am

    #730533

    With sql2005 (sp2) you could use login serverlevel triggers.

    With sql2000 i guess you're stuck to launching an "audit" job every x minutes which performs a sp_who2 and kills the most recent connection for that user

    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

  • August 30, 2007 at 2:42 pm

    #730597

    I think what I'm going to do is check the hostname and then gets a count of spids and makes a decision at that point as to whether any more should be allowed. They can only select from an indexed view, so I can call the script before that maybe. Thanks for your response.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply