Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

SQL Server 2005 SysAdmin Login Auditing


By:   |   Last Updated: 2008-11-14   |   Comments (15)   |   Related Tips: More > Triggers

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 (AuditEventID));
GO

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 master;
GO

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
            
(EventTimeServerLogin)
                
VALUES
            
(GETDATE(), ORIGINAL_LOGIN())
        
END;
    
END;
GO

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.


Last Updated: 2008-11-14


get scripts

next tip button



About the author
MSSQLTips author K. Brian Kelley K. Brian Kelley is a SQL Server author and columnist focusing primarily on SQL Server security.

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Sunday, April 23, 2017 - 9:47:06 AM - patsy25487988 Back To Top

Monday, October 21, 2013 - 8:33:10 AM - K. Brian Kelley Back To Top

Zacky, you'd have to use EVENTDATA(). There are some other tips that cover how to extract that sort of information using EVENTDATA().


Monday, October 21, 2013 - 12:23:02 AM - Zacky Back To Top

All,,please help me..

 

How can i get Host Name and IP with this Trigger??

All opinions will be greatly beneficial..


Thanks


Friday, August 31, 2012 - 12:32:46 AM - Tyson Then Back To Top

Caution with anwark's trigger.  I tried it and it locks out non sysadmin from using SQL server database.  Definitely not something you want to happen in Production.

More information here: http://blog.sqlauthority.com/2009/06/27/sql-server-fix-error-17892-logon-failed-for-login-due-to-trigger-execution-changed-database-context-to-master/

Taking ALZDBA's suggestion further I came up with:

CREATE TRIGGER [trigLogon_CheckForSysAdmin]
            ON ALL SERVER
            FOR LOGON
            AS
    BEGIN
if IS_SRVROLEMEMBER ('sysadmin') = 1
        BEGIN
        INSERT INTO DBAWork.dbo.AuditSysAdminLogin
            (EventTime, ServerLogin)
                VALUES
            (GETDATE(), ORIGINAL_LOGIN())
        END;
    END;

My testing seems to work okay with no Trigger Lockouts so far in Development.

 


Wednesday, July 28, 2010 - 5:59:27 AM - ALZDBA Back To Top
Why not just use the system function (in the server level trigger)

if IS_SRVROLEMEMBER ('sysadmin') = 1

begin

insert ...

end


Saturday, October 17, 2009 - 6:52:48 PM - K. Brian Kelley Back To Top

[quote user="tdinh"]Can we take this another step where we want to track all DDL and DML activities performed by sysadmin? Is this possible?[/quote]

To a certain extent. You can track all DDL changes. But in this case you'd be building DDL triggers in each database. You can track INSERT, UPDATE, and DELETE operations using standard DML triggers (INSTEAD OF and AFTER triggers). But you won't be able to track SELECTs using triggers. You'll either have to use the Audit object (if you're on SQL Server 2008 Enterprise Edition) or you'll have to rely on traces.

 


Friday, October 09, 2009 - 8:15:25 AM - tdinh Back To Top

Hello,

Can we take this another step where we want to track all DDL and DML activities performed by sysadmin? Is this possible?

 Thanks!


Monday, May 18, 2009 - 8:05:22 AM - anwark Back To Top

Hello! I think that i found the way to log users from windows group with sysadmin privileges.

 

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
                 AND sp.principal_id not in (285) -- akavila
           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

    -- SysAdmin from Windows Group
    DECLARE @user_windows tinyint;
    SELECT @user_windows =
           CASE WHEN nt_domain = '' THEN 0 ELSE 1 END
      FROM sys.dm_exec_sessions
     WHERE session_id = @@SPID

    IF @user_windows = 1
    BEGIN
      DECLARE @privilege_OUT varchar(10),
              @login varchar(100);

      SET @login = ORIGINAL_LOGIN()
      EXEC xp_logininfo
        @acctname = @login,
        @option = 'all',
        @privilege = @privilege_OUT OUTPUT
      IF @privilege_OUT = 'admin'
      BEGIN
        INSERT INTO DBAWork.dbo.AuditSysAdminLogin (EventTime, ServerLogin)
        VALUES (GETDATE(), ORIGINAL_LOGIN())
      END
    END
END;


Wednesday, May 13, 2009 - 2:31:33 PM - anwark Back To Top

  This trigger does not log a sysadmin logon from windows group. It will be nice that anybody fix this detail


Wednesday, March 04, 2009 - 10:49:17 PM - audi08 Back To Top

I don't want to monitor all the request that come to the sql cluster/server.

I have a sq 2005 cluster and i want to monitor if some one physically login to the server vis management studio or does some major

things like stop or Make change to the configuration in SQL 2005 cluster.

 

worst case at least audit who actually logged on to the SQL server cluster or sql 2005 server.

 

thanks

Audi08


Saturday, January 24, 2009 - 3:46:19 PM - K. Brian Kelley Back To Top

In 2005 and below, you have to pay careful attention to the SQL Server log. In 2008 you can use Policy Management to check and enforce settings. You can also have it just report on change.

 


Friday, January 23, 2009 - 2:31:53 PM - DBSQL Back To Top

How can I  audit /Monitor  if  some one Change the SQL server system configuration ?


Friday, November 21, 2008 - 8:01:12 AM - K. Brian Kelley Back To Top

That deletes it. If you want to leave it in place to be re-enabled later, you'll want to use DISABLE TRIGGER:

 

DISABLE TRIGGER trigLogon_CheckForSysAdmin ON ALL SERVER;

 


Friday, November 21, 2008 - 1:51:23 AM - Bas Back To Top

i found the solution

 drop trigger trigLogon_CheckForSysAdmin ON ALL SERVER


Friday, November 21, 2008 - 1:34:50 AM - Bas Back To Top

Hello

nice trigger but how can i Disable this trigger I can't find it on my server


Learn more about SQL Server tools