SQL Server 2005 SysAdmin Login Auditing

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

CREATE TRIGGER trigLogon_CheckForSysAdmin
            ON ALL SERVER
            FOR LOGON
            AS
    BEGIN
    IF EXISTS (
        SELECT sp.principal_id
            FROM sys.server_role_members srm
                    JOIN sys.server_principals sp
                    ON srm.member_principal_id = sp.principal_id
            WHERE role_principal_id = (
            SELECT principal_id 
                FROM sys.server_principals 
                WHERE NAME = ‘sysadmin’)
                AND ORIGINAL_LOGIN() = sp.NAME)
        BEGIN
        INSERT INTO DBAWork.dbo.AuditSysAdminLogin
            (EventTime, ServerLogin)
                VALUES
            (GETDATE(), ORIGINAL_LOGIN())
        END    ENDGO

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.

Related Posts

Leave a Reply

Your email address will not be published. Required fields are marked *