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.
An attempt to make a fourth connection, will be blocked.
The trigger error message will be written to the error log. Execute the following command to read the error log.
Execute sp_readerrorlog
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
ON ALL SERVER
FOR LOGON
AS
BEGIN
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
BEGIN
Print 'Fourth connection of ' +
@LoginName + ' blocked'
ROLLBACK
END
END
An attempt to make a fourth connection, will be blocked.
The trigger error message will be written to the error log. Execute the following command to read the error log.
Execute sp_readerrorlog
Please Help...I tried this video now i cant login to SQLserver and how can I revert it and login to SQLServer as I am locked out :(((( Thank you
ReplyDeleteHello Venkat, I need your help. I also tried the alter trigger and then i was getting error message and then i execute alter trigger without IF statement and I log out of server and tried to login, Now it is not letting me login into SQL server, I am beginner in sql and I have no idea how to fix it and login into sql server, your help will be much appreciated ...Thank you
ReplyDelete-----------------------------------------------------------------
The message I'm getting is like this follow:-
Connect to Server
Cannot connect tp LAPTOP-PRN\SQL2012
Additional Information:
Logon failed for login'LAPTOP-PRN\sonia'due to trigger execution.
Changed database context to 'master'.
Changed language setting to us_english.(Microsoft SQL Server,Error:17892)
----------------------------------------------------------------
(I went into configuration manager and stopped the all servers e.g reporting,ssis..., and restarted only one sqlserver. But still error message coming)
use master
ReplyDeletego
select * from sys.server_triggers order by create_date desc
drop trigger [tr_logonaudittriggers] on all server
after dropping it should work.