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
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
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.
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.
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.
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.
11 people are following this question.