Problem
I want to audit whenever a member of the sysadmin role logs on to my SQL Server. Auditing all successful logins provides that information, however, it logs every connection. The sysadmin logins are being lost amidst all the noise. How can I just track the sysadmin logins to my SQL Server instance?
Solution
In SQL Server 2005 Service Pack 2, Microsoft introduced logon triggers into the core functionality. Like 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 a particular role, such as the syadmin fixed server role, logs on.
In order to audit for members of the sysadmin fixed server role, we will 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 role or not. We can get the members by joining these two views together as shown below:
| SELECT sp.principal_id FROM sys.server_role_members srm INNER JOIN sys.server_principals sp ON srm.member_principal_id = sp.principal_id WHERE srm.role_principal_id = ( SELECT principal_id FROM sys.server_principals WHERE [Name] = ‘sysadmin’) |
This query will be the basis for our logon trigger. By adding an AND clause in our final set of code, we will be able to test whether or not the incoming logon is a member of the sysadmin fixed server role.
We will also need to 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 work database. For the purposes of this example, I am going to assume the table can be stored in a DBA database by the name of DBAWork. Here is the associated code:
| CREATE TABLE dbo.AuditSysAdminLogin (AuditEventId INT IDENTITY(1,1) NOT NULL, EventTime DATETIME NOT NULL, ServerLogin NVARCHAR(100) NOT NULL, CONSTRAINT PK_AuditSysAdminLogin PRIMARY KEY CLUSTERED (AuditEventIDGO |
Once we have the audit table, we can create our logon trigger. The basic syntax for a logon trigger is similar to a DDL trigger as shown below:
| CREATE TRIGGER <trigger name> ON ALL SERVER FOR LOGON AS <SQL Statements> |
Following this format and using the query identified above to help identify who is a member of the SysAdmin role, the only aspect we are missing is a way to identify the logon. There is a system function, ORIGINAL_LOGIN(), which provides that information. Putting it all together, here is our logon trigger:
USE masterGO |
Next Steps
- Be sure to have SQL Server 2005 SP2 or later installed in order to have this functionality work properly. If you are not sure which version of SQL Server you have check out this tip (How to tell what SQL Server version you are running).
- This tip provides a simple approach to capture the logins of SQL Server SysAdmins, but can be easily modified for other groups, roles or logins.
- The logic can also be modified to prevent logins from accessing the SQL Server instance during particular hours, to limit the number of sessions, etc. So think about some of the needs you have in your environment and consider this tip as a stepping stone to address those needs.
- As you experiment with this logic, please provide your problems and solutions in the forum below. We would love to hear how creative you were with the opportunities available with this favor of SQL Server triggers.
