How we can restrict the concurrent user session in SQL Server 2005

Question

How we can restrict the concurrent user session in SQL Server 2005

vishalkumarjain

SSC Rookie

Points: 41
More actions
April 15, 2009 at 6:37 am

#131280

How we can restrict the concurrent user session in SQL Server 2005.
If I want that only 20 users can excess my server at any moment (concurrent) and as 21st user try to connect, either he/she should get a message and get disconnected
or
any event/trigger in system which should check that on a time interval and if found excess connected user, kill the last connected user session.

Viewing 7 posts - 1 through 7 (of 7 total)

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

SQLZ SSChampion Points: 12872 More actions · Answer 1

You can use sp_configure to set the 'user connections' configuration option to limit the server to a maximum of 20 users although I can't think of why you'd ever want to do this.

I suppose if you really wanted to you could write something that checks the sys.dm_exec_sessions dmv and kills the most recent sessions when the number of user sessions exceeds 20 but I think that's a really bad idea.

Personally, if you really really need to limit the number of connections to 20 then I'd consider doing it through the application rather than through SQL Server as I can't think of why you'd want to limit SQL Server connections to just 20 and it could be a bad move.

Karl
source control for SQL Server

maechismo_8514 SSCoach Points: 17510 More actions · Answer 2

It is actually, Why you want to limit the number of users anyway? Is there any performance problem on your server, there are different approaches to that but limiting the number of users would not be a good idea as mentioned.

vishalkumarjain SSC Rookie Points: 41 More actions · Answer 3

Thats true that actually there is no need to do this from SQL Server. It can be easily managed by application. but I want to do it from SQL Server because one of our user got admin license of our application which provide unlimited acess of application. therefore now I want to restrict extra usage at backend/database level.

Please provide me complete solution, if any exist.

Thanks

SQLZ SSChampion Points: 12872 More actions · Answer 4

The solution is provided for you in my first post but, as I mentioned, this is a bad idea and I seriously advise against it. You still haven't explained why you want to do this by the way - I just can't see what you're trying to achieve by limiting the number of concurrent users to 20.

Karl
source control for SQL Server

vishalkumarjain SSC Rookie Points: 41 More actions · Answer 5

I am not able to do that , what you said. Please give me full information and method to do the same

Johan Bijnens SSC Guru Points: 135555 More actions · Answer 6

So, you're not entitled to restrict the instances number of connections to the number you want.

You could use logon triggers to do just that for you.

You could set it up to only respond for certain sqluserids or if you have no control to that or use windows authentication, you might be able to use the application name your application provides in the connection string. (You have control over that !)

Check articles for "logon triggers" at SSC.

or http://sqljunkies.com/WebLog/ktegels/archive/2006/11/09/25306.aspx

Keep in mind logon triggers work at instance level !

and can be disabled.

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