Auditing – SirSQL.net

Some say logon some say login. I tend to use them both (frequently within the same sentence). SQL Server really doesn’t help things by having you create a logon trigger that can do things with logins (see where I’m going here?).

Either way, we’re performing a server migration and needed to find out all the logins and clients that are connecting to the SQL instance. I also wanted to capture this data to a table to make it easy to report against (eliminating turning on successful logins). The ideal solution for me was to use a logon trigger, and as the instance in question is running SQL 2008 this worked out great.

I created a login, table of holding, table of rollup holding and granted the relevant permissions so that I could capture the data. Then I created the actual login logon trigger itself which captured the logon login, host name, application name and datetime information and logged the data into the table.

I added a procedure and scheduled job to rollup the data nightly so as to prevent the table from getting too large (which would be quite easy to do on a big server).

There are no primary keys and only a single clustered index. It’s all very light. If you are looking to capture logon/login data then this is a good starting point. Code below…

USE master 
go
--create the login which will be used to log the data
CREATE LOGIN LoginLogging WITH PASSWORD = 'somepassword123!@#' ;
GRANT VIEW SERVER STATE TO LoginLogging ;
 
 
--change this to the database of your choosing
USE DBAdmin
go
--create the user matching the login
CREATE USER LogonLogging FOR LOGIN LogonLogging ;
 
--create the table that will hold the data
CREATE TABLE LogonList
    (
      LoginName NVARCHAR(256) ,
      HostName NVARCHAR(256) ,
      ApplicationName NVARCHAR(256) ,
      LoginDate DATETIME2
    ) ;
CREATE INDEX CI ON dbo.LogonList(LoginDate)
 
CREATE TABLE LogonListDailyRollup
    (
      LoginName NVARCHAR(256) ,
      HostName NVARCHAR(256) ,
      ApplicationName NVARCHAR(256) ,
      LoginDate DATE,
      LoginCount INT
    ) ;
    
    
--allow the login to insert data to the table    
GRANT INSERT ON dbo.LogonList TO LogonLogging ;    
 
--proc to rollup the data daily to keep space low and ease reporting
CREATE PROCEDURE RollupLogonData
 
AS
 
SET NOCOUNT ON
 
BEGIN TRY
    BEGIN TRAN
 
    INSERT  INTO dbo.LogonListDailyRollup
            ( LoginName ,
              HostName ,
              ApplicationName ,
              LoginDate ,
              LoginCount
            )
            SELECT  LoginName ,
                    HostName ,
                    ApplicationName ,
                    CONVERT(DATE, LoginDate) AS LoginDate ,
                    COUNT(*) AS LoginCount
            FROM    dbo.LogonList
            WHERE   LoginDate < CONVERT(DATE, GETDATE())
            GROUP BY LoginName ,
                    HostName ,
                    ApplicationName ,
                    CONVERT(DATE, LoginDate);
 
 
    DELETE  FROM dbo.LogonList
    WHERE   LoginDate < CONVERT(DATE, GETDATE());
 
END TRY
BEGIN CATCH
    SELECT  ERROR_MESSAGE()
    ROLLBACK TRAN
END CATCH
COMMIT TRAN
 
GO
 
 
 
--db context back to master for creating the login trigger
USE master 
go
 
CREATE TRIGGER LogLogons ON ALL SERVER
    WITH EXECUTE AS 'LogonLogging'
    FOR LOGON
AS
    BEGIN
        INSERT  INTO DBAdmin.dbo.LogonList
                ( LoginName ,
                  HostName ,
                  ApplicationName ,
                  LoginDate 
                )
                SELECT  ORIGINAL_LOGIN() ,
                        HOST_NAME() ,
                        APP_NAME() ,
                        GETDATE()
                WHERE   ORIGINAL_LOGIN() NOT LIKE N'NT SERVICE%'
    END ;
 
GO

SirSQL.net

Tag: Auditing

Logging Logons (or is that logins?)

Central Auditing Of SQL Permissions Scripts