How to Audit Login Changes on a SQL Server

By: K. Brian Kelley | Comments (2) | Related: > Auditing and Compliance

Problem

I've been charged with coming up with a quick solution to audit login changes on my SQL Servers. However, there's no budget so I'm going to have to come up with basic scripts and the like to do the work. Is this tip we cover a solution for you to audit login changes.

Solution

Developing a solution to detect simple changes to logins is fairly easy and straight forward. For instance, if you're just charged with detecting what logins have been created, deleted, or modified since the last audit, a purely T-SQL solution can be implemented quickly and easily. We'll tackle something that provides slightly more information in this tip.

First, the basic tables we'll need to do the auditing. We'll need a table to store the current scan information as well as a table to store our scan history. We'll actually compare the two tables in order to determine what has changed between audits. We'll also need a table to track when we did our scans.

Here are the three tables:

CREATE TABLE dbo.AuditScan (
 AuditScanID INT IDENTITY(1,1) NOT NULL,
 ScanStartDate datetime NOT NULL,
 ScanEndDate datetime NULL,
 CONSTRAINT PK_AuditScan PRIMARY KEY CLUSTERED (AuditScanID)
);
GO 


CREATE TABLE dbo.CurrentLoginScan (
 [sid] VARBINARY(85) NOT NULL,
 [name] sysname NOT NULL,
 [type] CHAR(1) NOT NULL,
 create_date DATETIME,
 modify_date DATETIME,
 AuditScanID INT NOT NULL,
 CONSTRAINT PK_CurrentLoginScan PRIMARY KEY CLUSTERED ([sid]),
 CONSTRAINT FK_CurrentLoginScan_AuditScan FOREIGN KEY (AuditScanID) 
 REFERENCES dbo.AuditScan (AuditScanID)
);
GO 


CREATE TABLE dbo.LoginScanRecord (
 [ScanLogID] INT NOT NULL IDENTITY(1,1),
 [AuditScanID] INT NOT NULL,
 [sid] VARBINARY(85) NOT NULL,
 [name] sysname NOT NULL,
 [type] CHAR(1) NOT NULL,
 create_date DATETIME NOT NULL,
 modify_date DATETIME NOT NULL,
 CONSTRAINT PK_LoginSCanRecord PRIMARY KEY CLUSTERED (ScanLogID),
 CONSTRAINT FK_LoginScanRecord_AuditScan FOREIGN KEY (AuditScanID) 
 REFERENCES dbo.AuditScan (AuditScanID)
);
GO

Now we'll need a couple of stored procedures to mark the start and stop of each audit.

CREATE PROC dbo.StartAuditScan 
  @AuditScanID INT OUTPUT
AS
BEGIN
  SET NOCOUNT ON;

  
  DECLARE @AuditScanIDTable TABLE(AuditScanID INT);

  
  INSERT INTO dbo.AuditScan (ScanStartDate) 
    OUTPUT INSERTED.AuditScanID INTO @AuditScanIDTable
  VALUES (GETDATE());

  
  SET @AuditScanID = (SELECT AuditScanID FROM @AuditScanIDTable);
END;
GO 


CREATE PROC dbo.EndAuditScan
  @AuditScanID INT
AS
BEGIN
  SET NOCOUNT ON;

  
  UPDATE dbo.AuditScan
  SET ScanEndDate = GETDATE()
  WHERE AuditScanID = @AuditScanID;
END;
GO

And finally, we'll need the stored procedure that does the audit itself. It's going to do the following:

Start the audit.
Retrieve the pertinent information from the sys.server_principals view into the CurrentLoginScan table.
Compare to see what new logins have been created since the last audit.
Compare to see what logins have been deleted since the last audit.
Compare to see what logins show a change since the last audit.
End the audit.
Report the findings.

Here's the stored procedure that accomplishes the above.

CREATE PROC dbo.ExecuteLoginScan
AS
BEGIN
  SET NOCOUNT ON;

  
  DECLARE @CurrentScanID INT;
  DECLARE @PreviousScanID INT;

    
  SET @PreviousScanID = (SELECT MAX(AuditScanID) FROM dbo.AuditScan);
  EXEC dbo.StartAuditScan @CurrentScanID OUTPUT;

    
  EXEC dbo.EndAuditScan @CurrentScanID;

  
  INSERT INTO dbo.CurrentLoginScan
  ([sid], [name], [type], create_date, modify_date, AuditScanID)
  SELECT [sid], [name], [type], create_date, modify_date, @CurrentScanID
  FROM master.sys.server_principals;

    
  CREATE TABLE #LoginScanReport (
    Change VARCHAR(20) NOT NULL,
    [sid] VARBINARY(85) NOT NULL,
    [Current_Name] sysname NULL,
    [Old_Name] sysname NULL,
    [Current_Type] CHAR(1) NULL,
    [Old_Type] CHAR(1) NULL,
    [Current_Create_Date] DATETIME NULL,
    [Old_Create_Date] DATETIME NULL,
    [Current_Modify_Date] DATETIME NULL,
    [Old_Modify_Date] DATETIME NULL
  );

    
  -- Find new logins
  INSERT INTO #LoginScanReport 
  (Change, [sid], Current_Name, Current_Type, Current_Create_Date, Current_Modify_Date)
  SELECT 'Added', Curr.[sid], Curr.[name], Curr.[type], Curr.create_date, Curr.modify_date
  FROM dbo.CurrentLoginScan Curr
    LEFT JOIN (SELECT [sid], [name], [type], create_date, modify_date 
               FROM dbo.LoginScanRecord 
               WHERE AuditScanID = @PreviousScanID) Old
      ON Curr.[sid] = Old.[sid]
  WHERE Old.[sid] IS NULL;

  
  -- Find deleted logins
  INSERT INTO #LoginScanReport 
  (Change, [sid], Old_Name, Old_Type, Old_Create_Date, Old_Modify_Date)
  SELECT 'Deleted', Old.[sid], Old.[name], Old.[type], Old.create_date, Old.modify_date
  FROM dbo.CurrentLoginScan Curr
    RIGHT JOIN (SELECT [sid], [name], [type], create_date, modify_date 
               FROM dbo.LoginScanRecord 
               WHERE AuditScanID = @PreviousScanID) Old
      ON Curr.[sid] = Old.[sid]
  WHERE Curr.[sid] IS NULL;

      
  -- Find Changed Logins
  INSERT INTO #LoginScanReport 
  (Change, [sid], Current_Name, Current_Type, Current_Create_Date, Current_Modify_Date,
                  Old_Name, Old_Type, Old_Create_Date, Old_Modify_Date)
  SELECT 'Modified', Curr.[sid], Curr.[name], Curr.[type], Curr.create_date, Curr.modify_date,
                     Old.[name], Old.[type], Old.create_date, Old.modify_date
  FROM dbo.CurrentLoginScan Curr
    JOIN (SELECT [sid], [name], [type], create_date, modify_date 
          FROM dbo.LoginScanRecord 
          WHERE AuditScanID = @PreviousScanID) Old
      ON Curr.[sid] = Old.[sid]
  WHERE (Curr.[name] <> Old.[name]) OR (Curr.[type] <> Old.[type]) 
         OR (Curr.create_date <> Old.create_date) OR (Curr.modify_date <> Old.modify_date);

           
  INSERT INTO dbo.LoginScanRecord
  (AuditScanID, [sid], [name], [type], create_date, modify_date)
  SELECT AuditScanID, [sid], [name], [type], create_date, modify_date
  FROM dbo.CurrentLoginScan;

    
  DELETE FROM dbo.CurrentLoginScan;

  
  SELECT Change, [sid], Current_Name, Old_Name, Current_Type, Old_Type, 
    Current_Create_Date, Old_Create_Date, Current_Modify_Date, Old_Modify_Date
  FROM #LoginScanReport;  
END;
GO

Next Steps

Look at the SQL Server-provided tables and catalog views for login information.
Review how to use PowerShell to audit sysadmin level logins on multiple SQL Servers.
Learn how to implement an audit of password age for your SQL Server-based logins.
Know how to map between logins and database users.

About the author

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

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Monday, December 23, 2013 - 8:24:26 AM - Konstantin	Back To Top (27870)
It doesn't track properly track only new changes related to system roles membership, but it doesn't track for example if I map my login to a database as db_owner or writer

Friday, August 3, 2012 - 3:46:54 AM - Mike	Back To Top (18898)
Good article..

How to Audit Login Changes on a SQL Server

Problem

Solution

Next Steps

About the author

Comments For This Article