I'm sure you've come across a scenario where at a new client or employer you want to embark on a SQL Server Login cleanup. But a lot of the logins are from the dark ages and the current IT staff don't know which logins are used by who and for what.
Well there are two ways to view which logins are logging onto the SQL server and when: The one way is to enable successful and failed logon attempts at the SQL server level. This will write an entry to the SQL Error Log for every failed or successful logon. On a busy server this would make the error log huge. Besides that, the audit information is in a flat text file, which can be viewed, but not summarised easily.
Another way to audit logons to your SQL Server is to enable a server wide Logon Trigger, which as you know is available in SQL 2005 & 2008. See BOL for more details. This trigger exposes some XML Data about the logon and this data is made accessible using the EVENTDATA function.
The solution I've written to audit and summarize logon information uses a logon trigger, two logon Audit tables and a daily summarizing script. I've created these tables in the Master database, but they should ideally be created in a user database. The first table, AuditLogin stores all the data that results from the logon trigger firing. I've chosen to store ServerName, EventTime, ServerLogin, ClientHost, LoginType and IsPooled event data. The trigger basically writes these values to the AuditLogin table for a LOGON event. Code:
/****** Object: DdlTrigger [trigLogon] Script Date: 04/06/2009 10:57:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [trigLogon]
ON ALL SERVER
FOR LOGON
AS
BEGIN
declare @LogonTriggerData xml
SET @LogonTriggerData = eventdata() ;
INSERT INTO AuditLogin
(ServerName, EventTime, ServerLogin, Col_ClientHost, Col_LoginType, IsPooled)
(SELECT @@SERVERNAME, @LogonTriggerData.value('(/EVENT_INSTANCE/PostTime)[1]', 'datetime'), ORIGINAL_LOGIN(), @LogonTriggerData.value('(/EVENT_INSTANCE/ClientHost)[1]', 'varchar(50)'), @LogonTriggerData.value('(/EVENT_INSTANCE/LoginType)[1]', 'varchar(50)'), @LogonTriggerData.value('(/EVENT_INSTANCE/IsPooled)[1]', 'bit'))
END;
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
ENABLE TRIGGER [trigLogon] ON ALL SERVER
This is all good and well, now we know who is logging onto our Server and when and from where. But this trigger firing can fill a large hard disk quickly. On a busy server there may be millions of entries into this Audit table daily. This is mostly as a result of connection pooling and how a connection is kept alive by an application server like IIS. So for the most part I don't worry to much about huge amounts of logons from pooled connections from application servers. The logon entries I'm more interested in are for non pooled connections.
So what we need, from an audit summary point of view is a distinct record for each logon from a distinct host and the logons pooled status, and the last time this logon logged in and how many times it logged in. This will give me some valuable information from the millions of records or raw logon data. Information like which logins have not logged in since Logon Auditing has been enabled, which generic logins are being used from which hosts and which non pooled logins are logging in the most. From my original mission of establishing which logins are used and how often, we can now determine this.
Now I need to take the daily logon audit information and summarize it and merge todays summarized information with the historic logon information. This is a very common scenario in a Data Warehouse environment. I NEED to summarize and TRUNCATE my AuditLogin table after I've summarized the data in it because of the huge amount of data that goes into this table every day. Knowing that login x logged on at a particular time yesterday is of little value. So I don't need the detail data, unless there is a security breach. Summarize, Merge to History, Truncate. Treat it mean, keep it clean.
The below code uses some incremental load logic I've gleened from my Data Warehouse experience. Basically the first full load and subsequent incremental loads of my AuditLoginHistory table use the same code. Thats pretty handy. I can run the same code to load the history table from the first load.
Basically each record in the history table is identified by Login, Host and Pooled. The two other fields are LoginCount and LastLoggedIn. So what the below code does is first summarize todays logon information. Then it compares it to the History information. If the same record exists, the LoginCount and LastLoggedIn is updated. If the record doesn't exist, a new one is inserted into the history table.
Summarizing Code:
with LoginCnt(ServerLogin, LoginCount, IsPooled, Host)
AS
(
select ServerLogin, count(*) AS LoginCount, IsPooled, Col_ClientHost from auditlogin
group by ServerLogin, IsPooled, Col_ClientHost
)
, LastLogin(ServerLogin, Host, LastLoggedIn, IsPooled)
AS
(
select serverlogin, Col_ClientHost, IsPooled, max(EventTime) AS LastLoggedIn from AuditLogin
group by ServerLogin, Col_ClientHost, IsPooled
)
login trigger
--SELECT lc.ServerLogin, lc.Host, ll.LastLoggedIn, lc.LoginCount, lc.IsPooled
--FROM LoginCnt lc INNER JOIN LastLogin ll
--ON lc.ServerLogin = ll.ServerLogin
--AND lc.Host = ll.Host
SELECT lc.ServerLogin, lc.Host, ll.LastLoggedIn, lc.LoginCount, lc.IsPooled
INTO #AuditTmp
FROM LoginCnt lc INNER JOIN LastLogin ll
ON lc.ServerLogin = ll.ServerLogin
AND lc.Host = ll.Host
ORDER BY ISPooled ASC, LoginCount DESC
GO
BEGIN TRAN
DECLARE @SL VARCHAR(128)
DECLARE @Host VARCHAR(30)
DECLARE @LastLoggedIn DATETIME
DECLARE @LC INT
DECLARE @IP BIT
Declare AuditCur CURSOR FOR
SELECT ServerLogin, Host, LastLoggedIn, LoginCount, IsPooled FROM #AuditTmp
OPEN AuditCur
FETCH NEXT FROM AuditCur INTO @SL, @Host, @LastLoggedIn, @LC, @IP
WHILE @@FETCH_STATUS = 0
BEGIN
IF (SELECT COUNT(*) FROM AuditLoginHistory
WHERE ServerLogin = @SL AND Host = @Host AND IsPooled = @IP) = 1
BEGIN
UPDATE AuditLoginHistory
SET LastLoggedIn = @LastLoggedIn,
LoginCount = LoginCount + @LC
WHERE ServerLogin = @SL AND Host = @Host AND IsPooled = @IP
END
ELSE
BEGIN
INSERT AuditLoginHistory(ServerLogin, Host, LastLoggedIn, LoginCount, IsPooled)
VALUES(@SL, @Host, @LastLoggedIn, @LC, @IP)
END
FETCH NEXT FROM AuditCur INTO @SL, @Host, @LastLoggedIn, @LC, @IP
END
CLOSE AuditCur
DEALLOCATE AuditCur
DROP TABLE #AuditTmp
TRUNCATE TABLE auditlogin
IF @@ERROR <> 0
ROLLBACK TRAN
ELSE
--PRINT 'Successful'
COMMIT TRAN
Below is the code for the two Audit tables:
USE [master]
GO
/****** Object: Table [dbo].[AuditLogin] Script Date: 01/19/2009 08:20:30 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[AuditLogin]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[AuditLogin](
[AuditEventId] [int] IDENTITY(1,1) NOT NULL,
[ServerName] [nvarchar](100) NOT NULL,
[EventTime] [datetime] NOT NULL,
[ServerLogin] [nvarchar](100) NOT NULL,
[Col_ClientHost] [varchar](50) NULL,
[Col_LoginType] [varchar](50) NULL,
[IsPooled] [bit] NULL,
CONSTRAINT [PK_AuditLogin] PRIMARY KEY CLUSTERED
(
[AuditEventId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_PADDING OFF
GO
GRANT INSERT ON [dbo].[AuditLogin] TO [public]
GO
USE [master]
GO
/****** Object: Table [dbo].[AuditLoginHistory] Script Date: 04/06/2009 10:37:55 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[AuditLoginHistory](
[ServerLogin] [varchar](128) NULL,
[Host] [varchar](30) NULL,
[LoginCount] [int] NULL,
[LastLoggedIn] [datetime] NULL,
[IsPooled] [bit] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
Lastly, a caveat to make sure the trigger fires and doesn't cause logon failures: Ensure that all the objects that get created are accessible to public. Otherwise logons will fail because the user logging in causes the trigger to fire and the write to the Audit table is done in the context of the user thats logging on.
