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.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s