13. 08. 2009 NetEye Blog Admin Uncategorized

Microsoft SQL Server Login Auditing with Logon Triggers

All time the same problem. We will audit whenever a member of one of the SQL Server administrative roles logs on to my SQL Server Instance, the standard SQL audit “Successful logins only” provides this information, the SQL Server logs every connection, but without a focus of the members from the SQL server administrative roles. If I use the options “Enable Common Criteria compliance” or “Enable C2 audit tracking” both standards from the US Governance, we get many Information for all actions from the SQL Server ( on my SQL Server in 15 minutes 160 MB on log files only for C2 audit!) but no separate information to the administrative logins are provided. We have out of the box no options to see only the members with administrative rights to logon on to the SQL Server Instance.

Here a overview of the standard options for auditing the SQL Server, Login auditing, Common Criteria compliance and C2 audit tracking. For the basic SQL auditing enable “Login Auditing” and “Common Criteria compliance”, with the option “Common Criteria compliance” we have also a very good statistic from the logon over a time period.

Can we track the administrative logins to my SQL Server instance? Yes we can!

In SQL Server 2005 SP 2, Microsoft introduced logon triggers into the core functionality from the SQL Server. DDL and DML triggers, these triggers fire on particular events, in this case, whenever a logon to the SQL Server instance occurs. We can use a logon trigger to audit when members of one of the SQL Server administrative role, such as example the “syadmin” server role, logs on.

In this example for audit administrative logins on the SQL Server we will audit the login from all members of the “sysadmin” role of the SQL Server. In order to audit the members of the “sysadmin” SQL Server role, we need to use two system views: sys.server_role_members and sys.server_principals. We will join these views when a logon event occurs to determine if the logon is a member of the “sysadmin” SQL Server role or not. We can get the members of the “sysadmin” SQL Server role by joining these two views together, see the following syntax:

This example query is the basis for our logon trigger. We will also need some place to record the event when it occurs. One of the easiest ways to do this is to use a table created for this purpose in a DBA database. For this example, I will create the table in a DBA database with the name of “DboAudit”. I create the database manually over the SQL Management Studio. After there we will create the necessary table in our database. Here is the associated syntax:

After creating the audit table in our DBA database, we are ready to create our logon trigger. The create syntax for a logon trigger is not so difficult:

Following this basis syntax and using the query identified above to help identify who is a member of the “SysAdmin” SQL Server role, the only aspect we are missing is a way to identify the logon. There is a SQL system function, ORIGINAL_LOGIN(), which provides this information. Putting all together, execute and here is the our new logon trigger:

That’s all. With this logon trigger all logon information’s for members of the SQL Server role “sysadmin” stored in our work database “DboAudit” on the SQL Server. If we need this information for logging or statistics we can provided this information over SQL very simple.

But, What is, if we need only information of logon from users with “dbo” rights on a dedicated database? No problem, it’s the same way over logon triggers only with different functions and code. More in the next.