Introduction
The Logon Trigger is used to catch the successful login entry by both Windows Authentication and SQL Server Authentication. By the use of logon triggers we can audit or control server sessions by tracking the login activity, restricting logins to SQL Server, or limiting or denying the number of login sessions for a specific user.
The logon triggers always run after the authentication phase, but before the user session is actually established. That means that trigger logon will not fire if authentication fails.
This article is to understand the use of Logon Trigger.
To understand it properly let’s take an example.
Example-1
By this example we can actually set a Logging Audit feature.
-- Create The Audit Database --
CREATE DATABASE AUD_Db
GO
USE AUD_Db
GO
-- Create Audit Table --
IF OBJECT_ID(N'dbo.tbl_LOGINAUD', N'U') IS NOT NULL
BEGIN
DROP TABLE dbo.tbl_LOGINAUD;
END
CREATE TABLE dbo.tbl_LOGINAUD
(
LoginUser VARCHAR(512),
AppName VARCHAR(max),
DBUser VARCHAR(512),
SPID INT,
LogonTime DATETIME)
GO
-- Create Logon Trigger --
IF OBJECT_ID(N'dbo.trg_SQLLogon', N'TR') IS NOT NULL
BEGIN
DROP TRIGGER AUD_Db.dbo.trg_SQLLogon;
END
GO
CREATE TRIGGER AUD_Db.dbo.trg_SQLLogon
ON ALL SERVER FOR LOGON
AS
BEGIN
INSERT INTO AUD_Db.dbo.tbl_LOGINAUD
(LoginUser, AppName, DBUser, SPID, LogonTime)
SELECT SYSTEM_USER, APP_NAME, USER, @@SPID, GETDATE();
END
GO
Example -2
For this we have to care a User in the name of test1
Now we create the Trigger
IF OBJECT_ID(N'dbo.trg_ConnectRestric', N'TR') IS NOT NULL
BEGIN
DROP TABLE [dbo].[trg_ConnectRestric];
END
GO
CREATE TRIGGER [dbo].[trg_ConnectRestric]
ON ALL SERVER
FOR LOGON
AS
BEGIN
DECLARE @ErrorTxt VARCHAR(128);
SET @ErrorTxt = 'Cannot allow login to "test1" outside of Business hours. ';
SET @ErrorTxt = @ErrorTxt + 'Please try again between business hours 10:00 and 18:00.';
IF ORIGINAL_LOGIN() = 'test1' AND
(DATEPART(HOUR, GETDATE()) < 10 OR DATEPART (HOUR, GETDATE()) > 18)
BEGIN
PRINT @ErrorTxt;
ROLLBACK;
END
END
GO
Hope you like it.
Posted by: MR. JOYDEEP DAS
You are going to have to show people how to delete or disable a logon trigger that fails. When a logon trigger fails, it locks EVERYONE out of the instance (it rolls back the transaction).
ReplyDeleteYour example 1 will fail because there is no INSERT permission on AUD_Db.dbo.tbl_LOGINAUD. You'll have to create a logon, execute the trigger as that logon and grant that logon INSERT permissions.
Also, the best practice for logon triggers to get information from EVENTDATA(). System functions may not give you the information you expect because the trigger fires before the session is established.
Thanks @ marcjellinek
DeleteCREATE TRIGGER trg_SQLLogon
ON ALL SERVER WITH Execute As ‘sa’ FOR LOGON
AS
BEGIN
...........................
............................
C:\Users\Joydeep>sqlcmd -S LocalHost -d master -A
Delete1> DROP TRIGGER trg_SQLLogon ON ALL SERVER
2> GO