How to create Logon trigger and implementation

Hello friends, today I am going to talk about one of the interesting feature of SQL server related to security audit. We can track login details using Logon triggers in SQL server versions 2005 and above.

Problem Statement:- If your client ask you, I want the details of successful logins made on production database yesterday to investigate who made the changes in table schema.

Solution:- The best solution of this problem would be to create a logon trigger at server level, and a login audit table which will contain all the details of successful login. Concept of logon trigger was introduced in SQL server 2005, so this feature will not be available prior versions of SQL server.

I have a database named Demo, where I will create a table Security_Audit, which will contain the details of logins. We will create the table by running below query.

Create table Security_Audit(Login_Name varchar(50),Login_Time datetime)

Now we will create a Logon trigger to track the login information by running below query.

Create trigger Trg_login_info on all server for logon

as

begin

      insert into demo.dbo.Security_Audit select SYSTEM_USER, GETDATE()

end

Lets check if login infomration is getting tracked or not. In the below screenshot we can see that every login is getting tracked into Security_Audit table irrespective of the fact if user is login to the server using windows authentication or SQL authentication.

Warning:- Before creating a logon trigger at server level, you need to remember one important factor.

Create trigger Trg_login_info on all server for logon

as

begin

      insert into demo.dbo.Security_Audit select SYSTEM_USER, GETDATE()

end

In the body of trigger, I have written fully qualified name of the Security_Audit table, which is “demo.dbo.Security_audit”, the fully qualified name indicates that this table exists in the “Demo” database. If we do not specify the fully qualified name of Security_Audit table(i.e. only Security_Audit), SQL sever will assume that this table exits in master database, which is not correct(cause we have created this table into Demo database. As a result, if a user try to login to the server, the above created trigger will fire automatically and try to find Security_Audit table in master database to insert the login information, but will failed as Security_Audit table doesn’t exits in master database. Hence user will not be able to login to the server. So please make sure that you are putting fully quallified name of the Security_audit table or if you are not using the fully qualified name then you should create the Security_Audit table in master database.

I hope you will find this article useful for you. Please let me know, if you have any question about this article.