Support us .Net Basics C# SQL ASP.NET ADO.NET MVC Slides C# Programs Subscribe Buy DVD

Logon triggers in sql server

Suggested Videos
Part 93 - Server-scoped ddl triggers
Part 94 - SQL Server trigger execution order
Part 95 - Audit table changes in sql server

In this video we will discuss Logon triggers in SQL Server.

As the name implies Logon triggers fire in response to a LOGON event. Logon triggers fire after the authentication phase of logging in finishes, but before the user session is actually established. 

Logon triggers can be used for
1. Tracking login activity
2. Restricting logins to SQL Server
3. Limiting the number of sessions for a specific login

Logon trigger example : The following trigger limits the maximum number of open connections for a user to 3. 

CREATE TRIGGER tr_LogonAuditTriggers
    DECLARE @LoginName NVARCHAR(100)

    Set @LoginName = ORIGINAL_LOGIN()

    IF (SELECT COUNT(*) FROM sys.dm_exec_sessions
         WHERE is_user_process = 1
         AND original_login_name = @LoginName) > 3
         Print 'Fourth connection of ' + @LoginName + ' blocked'

An attempt to make a fourth connection, will be blocked.
logon triggers in sql server

The trigger error message will be written to the error log. Execute the following command to read the error log.
Execute sp_readerrorlog

logon trigger example sql server

No comments:

Post a Comment

If you like this website, please share with your friends on facebook and Google+ and recommend us on google using the g+1 button on the top right hand corner.