Limiting connections

Question

Limiting connections

sunny.tjk

SSCertifiable

Points: 5041
More actions
June 10, 2010 at 12:22 pm

#140782

Is it possible to limit the number of connections for a login?
Thanks..

Viewing 5 posts - 1 through 4 (of 4 total)

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

yes you can, but you have to use a logon trigger to do it.

I'm sure there's more to it than the # of connections that you need to figure out business wise before you start blocking people.... each tab in SSMS is a connection, plus the object explorer as well in SSMS;

here's an example, but do not put this anywhere except a test machine till you know all of the ramifications....blocking out sa logins, for example, could lock EVERYONE that matters out.

this is untested but syntactically correct:

CREATE TRIGGER logon_trigger_Max_3Connections

ON ALL SERVER FOR LOGON

AS

BEGIN

if exists (SELECT count(*)

FROM sys.dm_exec_sessions

WHERE original_login_name =suser_name()

AND original_login_name != 'sa'

group by original_login_name

having count(*) > 3 )

BEGIN

--only block access if lots o connections and not sa

ROLLBACK

END

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

SQLRNNR SSC Guru Points: 281334 More actions · Answer 2

I agree with Lowell, don't use this anywhere except for a test environment for the time being. You will need to clearly define the connection requirements and how it should be interpreted based on business needs.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

sunny.tjk SSCertifiable Points: 5041 More actions · Answer 3

Fantastic Lowell....but I'm not sure what this "ON ALL SERVER FOR LOGON" does.

Lowell SSC Guru Points: 323518 More actions · Answer 4

sunny.tjk (6/10/2010)
Fantastic Lowell....but I'm not sure what this "ON ALL SERVER FOR LOGON" does.

ahh, that's our point...if you don't have a handle on LOGON triggers, read up on them, but keep this code away from anything except a test machine. this is one of those "tools", that if misused, gets you in the doghouse or worse when misapplied.

In SQL you can have triggers on the SERVER, on a DATABASE or on a TABLE or VIEW...., in order to do stuff based on specific events.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!