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 