Limiting the number connections to SQL Server database

If you would like to

1. Limit the number connections to SQL Server database.
2. Restrict connections to SQL server from tools like SSMS (or) You dont want non-sysadmins to connect with SQL Server except from application.

We can acheive all this by creating logon triggers.

Sample 1
=======
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;

Sample 2
=======
This trigger filters restricts other applications to access the database other than the  APPname (replace your application name with APPname)
Declare @role numeric(1)
select @role= IS_SRVROLEMEMBER (‘sysadmin’);
if @role1
begin
if (SELECT COUNT(*) FROM sys.dm_exec_sessions
            WHERE is_user_process = 1 AND
              program_name’APPName’ and
                original_login_name in (select suser_sname()))>0
Rollback;
end;

Note: we can use sp_configure ‘user connections’  to limit the number of connections to SQL Server.