« Logging Logons (or is that logins?) »

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