Sleepy CoderSQL Server 2005 SysAdmin Login Auditing
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 (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
(EventTime, ServerLogin)
VALUES
(GETDATE(), ORIGINAL_LOGIN())
END;
END;
GO
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
(EventTime, ServerLogin)
VALUES
(GETDATE(), ORIGINAL_LOGIN())
END;
END;
GO
