question

BilalHBhatt-9294 avatar image
0 Votes"
BilalHBhatt-9294 asked Cathyji-msft commented

max no of connection a user can create

Hello,

I am using SQL2016, want to know is their any limit on the connections a login/user can create once he is connected to the instance.
if yes, how to change this limit.?

Thanks

sql-server-general
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @BilalHBhatt-9294,

Did the replies could help you? If the response helped, do "Accept Answer". If it is not, please let us know. By doing so, it will benefit all community members who are having this similar issue.  Your contribution is highly appreciated.

0 Votes 0 ·
TomPhillips-1744 avatar image
1 Vote"
TomPhillips-1744 answered ErlandSommarskog commented

There is not a limit on the number of connections a user can create. There is a built-in limit to number of connections to the server 32767, which cannot be changed.

There is a max number of connections to a database setting, but that is 0 as default.
https://www.mssqltips.com/sqlservertip/5766/managing-maximum-number-of-concurrent-connections-in-sql-server/


You can create a trigger to limit the number of connections per user.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

You can create a trigger to limit the number of connections per user.

I like to add that just because you can, does not mean that you should. Such a login trigger is likely to cause more pain than benefit.

0 Votes 0 ·
Cathyji-msft avatar image
1 Vote"
Cathyji-msft answered

Hi @BilalHBhatt-9294,

want to know is their any limit on the connections a login/user can create once he is connected to the instance.

No, there is no limit on the connections of a login/user can create. But SQL Server allows a maximum of 32,767 user connections for a SQL instance. The actual number of user connections allowed also depends on the version of SQL Server that you are using, and also the limits of your application or applications and hardware. You can change the number using SSMS UI or T-SQL, refer to MS document Configure the user connections Server Configuration Option to get more.

You can use logon triggers to control server sessions, such as restricting logins to SQL Server, or limiting the number of sessions for a specific login. Below example restrict three user sessions created by login test.

 USE master;  
 GO  
 CREATE LOGIN login_test WITH PASSWORD = '3KHJ6dhx(0xVYsdf' MUST_CHANGE,  
     CHECK_EXPIRATION = ON;  
 GO  
 GRANT VIEW SERVER STATE TO login_test;  
 GO  
 CREATE TRIGGER connection_limit_trigger  
 ON ALL SERVER WITH EXECUTE AS 'login_test'  
 FOR LOGON  
 AS  
 BEGIN  
 IF ORIGINAL_LOGIN()= 'login_test' AND  
     (SELECT COUNT(*) FROM sys.dm_exec_sessions  
             WHERE is_user_process = 1 AND  
                 original_login_name = 'login_test') > 3  
     ROLLBACK;  
 END;

Refer to MS document Logon Triggers to get more.


If the response is helpful, please click "Accept Answer" and upvote it, thank you.



5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.