Summary
Keywords
Full Transcript
sql server logon trigger example sql server logon trigger audit In this video we will discuss Logon triggers in SQL Server. Healthy diet is very important both for the body and mind. If you like Aarvi Kitchen recipes, please support by sharing, subscribing and liking our YouTube channel. Hope you can help. https://www.youtube.com/channel/UC7sEwIXM_YfAMyonQCrGfWA/?sub_confirmation=1 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 Text version of the video http://csharp-video-tutorials.blogspot.com/2015/09/logon-triggers-in-sql-server.html Slides http://csharp-video-tutorials.blogspot.com/2015/09/logon-triggers-in-sql-server_13.html All SQL Server Text Articles http://csharp-video-tutorials.blogspot.com/p/free-sql-server-video-tutorials-for.html All SQL Server Slides http://csharp-video-tutorials.blogspot.com/p/sql-server.html All Dot Net and SQL Server Tutorials in English https://www.youtube.com/user/kudvenkat/playlists?view=1&sort=dd All Dot Net and SQL Server Tutorials in Arabic https://www.youtube.com/c/KudvenkatArabic/playlists
