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.?
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.
You can create a trigger to limit the number of connections per user.
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.
11 people are following this question.