Ever felt the need for knowing who is logging on to your SQL Server and at what time? Ever felt like restricting some of the specific users for certain time-period or firing a trace to track down user activity? Ever felt like limiting the number of concurrent connections from specific users?

 

Well, you can do all that now with SQL Server.

 

SQL Server introduced a new feature called Logon Triggers that allows you to fire a T-SQL, or a stored procedure in response to a LOGON event. You may use logon trigger to audit and control users by tracking login activity, restricting logins from accessing  SQL Server, or by limiting the number of sessions for specific logins. Logon Triggers are fired only after a login is successfully authenticated but just before the user session is actually established. All messages originating from inside the trigger (ex: messages, errors) from the PRINT statement are sent to the SQL Server error log.

 

NOTE: If the user authentication fails for any reason, then the Logon triggers are not fired.

 

Below example shows you how you can create a Logon trigger and send a message to SQL Server error log as soon as any user logs in:

 

CREATING A SERVER WIDE LOGON TRIGGER

CREATE TRIGGER OPS_LOGON

ON ALL SERVER

AFTER LOGON

AS

BEGIN

PRINT SUSER_SNAME() +' HAS JUST LOGGED IN TO '+UPPER(LTRIM(@@SERVERNAME))+' SQL SERVER AT '+LTRIM(GETDATE())

END

GO

 

LIMITING A LOGIN TO 5 CONCURRENT SESSIONS

CREATE TRIGGER OPS_LOGON

ON ALL SERVER WITH EXECUTE AS 'TORONTO\DAMIR'

FOR LOGON

AS

BEGIN

IF ORIGINAL_LOGIN()= 'TORONTO\DAMIR' AND

    (SELECT COUNT(*) FROM SYS.DM_EXEC_SESSIONS WHERE IS_USER_PROCESS = 1 AND ORIGINAL_LOGIN_NAME = 'TORONTO\DAMIR') > 5

    ROLLBACK;

END;

 

QUERYING SERVER LEVEL TRIGGERS

SELECT * FROM SYS.SERVER_TRIGGERS

GO

 

DROPPING OPS_LOGON SERVER LEVEL TRIGGER

DROP TRIGGER OPS_LOGON ON ALL SERVER

GO

 

DamirB-BlogSignature