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:   |   Updated: 2008-11-14   |   Comments (15)   |   Related: More > Triggers

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?

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
ServerLogin NVARCHAR(100) NOT NULL,

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

CREATE TRIGGER trigLogon_CheckForSysAdmin
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')
        INSERT INTO 

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..


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
if IS_SRVROLEMEMBER ('sysadmin') = 1
        INSERT INTO DBAWork.dbo.AuditSysAdminLogin
            (EventTime, ServerLogin)
            (GETDATE(), ORIGINAL_LOGIN())

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


insert ...


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


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


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]
          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)
      INSERT INTO DBAWork.dbo.AuditSysAdminLogin (EventTime, ServerLogin)

    -- 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
      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'
        INSERT INTO DBAWork.dbo.AuditSysAdminLogin (EventTime, ServerLogin)

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.




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:




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


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

Learn more about SQL Server tools