SQL Server Logon Trigger

Süleyman Kaş

5 years ago

Hello there,
User management is very important in our database systems. We prefer DBAs to create or use more windows authentication instead of sql authentication. But conditions may not always be what we want. Today, many applications use sql authentication to access the database. These SQL accounts are easily accessed via SSMS (SQL Server Management Studio), and anyone with sql user information will have access to the database. To avoid this, we will write a trigger.

We will block database access through Management Studio with the user accounts we set in this trigger. Users will receive an error message via Management Studio when they try to login with these accounts.

CREATE TRIGGER [DISABLE_LOGON_APP]
ON ALL SERVER 
FOR LOGON
AS
BEGIN

   DECLARE @program_name nvarchar(128)
   DECLARE @host_name nvarchar(128)

   SELECT @program_name = program_name, 
      @host_name = host_name
   FROM sys.dm_exec_sessions AS c
   WHERE c.session_id = @@spid


   IF ORIGINAL_LOGIN() IN('sqluser1','sqluser2','testuser') 
      AND @program_name LIKE '%Management%Studio%' 
   BEGIN
      RAISERROR('This login is for application use only.',16,1)
      ROLLBACK;
   END

CREATE TRIGGER [DISABLE_LOGON_APP]

ON ALL SERVER

FOR LOGON

BEGIN

DECLARE @program_name nvarchar(128)

DECLARE @host_name nvarchar(128)

SELECT @program_name = program_name,

@host_name = host_name

FROM sys.dm_exec_sessions AS c

WHERE c.session_id = @@spid

IF ORIGINAL_LOGIN() IN('sqluser1','sqluser2','testuser')

AND @program_name LIKE '%Management%Studio%'

BEGIN

RAISERROR('This login is for application use only.',16,1)

ROLLBACK;

END