Creating A Logon Trigger To Track When A Specific Account Is Connecting To A SQL Server
I was recently tasked with tracking down which applications were using a specific SQL account. The account in question was a legacy account that was used as a semi ‘sa’ level account a while back. Not knowing which applications had been configured to use this account I decided to create a logon trigger to record every instance where this account was connecting to a particular SQL Server and write those records to a table so they could be easily sorted and reported upon.
The steps outlined below will guide you through the necessary steps of creating an audit table and logon trigger to monitor each time a certain account makes a connection to your SQL Server.
The first step is to create a table to store the data. I would recommend creating this table on the same server that you will be monitoring, though if you have a linked server configured, it’s not technically necessary.
USE [DATABASENAME] --specify database name
GO
--CREATE AUDIT TABLE
CREATE TABLE user_audit_table
(
ID INT NOT NULL CONSTRAINT PK_LogonAudit_ID PRIMARY KEY CLUSTERED IDENTITY(1,1),
UserName NVARCHAR(255),
ApplicationName NVARCHAR(255),
Host NVARCHAR(255),
SPID INT NOT NULL,
LogonDate DATETIME,
SessionUser NVARCHAR(255)
)
The script above will create a table which will store the UserName
, ApplicationName
, Host
(Computer), SPID
, LogonDate
and SessionUser
(typically dbo) of each connection made. The UserName
will always reflect the account that will be configured within the logon trigger.
The next step is to GRANT
the necessary INSERT
permissions to the table. Since this is simply and audit table, no special permissions are needed. The following script will suffice.
GRANT INSERT ON dbo.user_audit_table TO PUBLIC;
Once permissions have been assigned the next step is to create the trigger. The script outlined below will get you started. You’ll need to configure the trigger to include your specific account and Database Name. Since the table resides in a User Database and the trigger resides as a server trigger you’ll have to provide the full connection string to the database where the audit table resides. I’ve also included an additional WHERE
clause should you want to ignore certain applications. I found this useful as I was already aware of certain applications using the account and did not want to include them in my results.
CREATE TRIGGER user_audit ON ALL SERVER FOR LOGON
AS
BEGIN
IF SUSER_SNAME() = 'USER_ACCOUNT' --ASSIGN USER ACCOUNT HERE
INSERT INTO DATABASENAME.dbo.user_audit_table
(UserName,ApplicationName,Host,SPID,LogonDate,SessionUser)
SELECT SUSER_SNAME(),APP_NAME(),HOST_NAME(),@@SPID,GETDATE(),
SESSION_USER
--WHERE APP_NAME() NOT IN ('') --EXCLUDE CERTAIN APPLICATIONS
END
The final step is to enable the trigger.
ENABLE TRIGGER user_audit ON ALL SERVER;
Once the above steps have been completed you are ready to begin querying the audit table. Again, you’ll need to provide the name of the database where your audit table exists.
It’s worth noting that, depending on the account you are logging, the audit table can potentially populate with a very large number of records quickly. This was the case when I enabled the trigger. Keep a close eye on the amount of data going into the table and disable the trigger if necessary.
USE [DATABASENAME]
GO
SELECT * FROM user_audit_table
Unlike table triggers, a logon trigger is applied to ALL SERVER
so it will reside in a different location. If you’re unfamiliar with these types of triggers you can easily locate it by navigating to the Server Objects node in Object Explorer in SQL Management Studio and selecting Triggers.
Finally, to simplify your development and testing in a lab environment, here are a list of quick scripts to mange disabling and dropping the trigger and associated table should it be necessary.
DISABLE TRIGGER user_audit ON ALL SERVER;
DROP TRIGGER user_audit ON ALL SERVER;
TRUNCATE TABLE user_audit_table
DROP TABLE user_audit_table