Auditing of database connections in SQL Server 2008 R2 We are finally planning to close down our last SQL Server 2008 R2 server. Before we can proceed with this we need to track what users are connecting to which databases. We have tried different options like using SQL Audit and checking the logins in the error log but all of these are missing the information to which database the connection are made. We did also look into using the LOGON trigger but as it is triggered when the user logins to the instance it will not track if the user afterwards change the database.
In newer versions I have used Extended Events for this kind of auditing but the sqlserver.login events is not yet available in this database version. It seems like it should be possible to use Profiler for this but I'm worried about continuously running trace specially on this old server. Are there any other options that I have missed or is Profiler the only option?
I faced a similar problem a couple of years ago and created a SQL Agent job to run the query below every few minutes. The query polled
sys.dm_exec_sessions, sys.sysprocesses, sys.dm_exec_connections
it's a bit rough and ready but it met my purposes and might help you too
CREATE TABLE [dbo].[log_db_connections](
[Database] [nvarchar](128) NULL,
[Login] [nvarchar](128) NOT NULL,
[nt_user_name] [nvarchar](128) NULL,
[LoginTime] [datetime] NOT NULL,
[CollectionTime] [datetime] NOT NULL CONSTRAINT [DF_CollectionTime] DEFAULT GETDATE(),
[Program] [nvarchar](128) NULL,
[client_interface_name] [nvarchar](32) NULL,
[Host] [nvarchar](128) NULL,
[client_net_address] [varchar](48) NULL,
[client_tcp_port] [int] NULL,
[net_transport] [nvarchar](40) NOT NULL,
[net_packet_size] [int] NULL,
[protocol_type] [nvarchar](40) NULL,
[endpoint_id] [int] NULL,
[encrypt_option] [nvarchar](40) NOT NULL,
[auth_scheme] [nvarchar](40) NOT NULL,
[num_reads] [int] NULL,
[num_writes] [int] NULL
) ON [PRIMARY]
GO



-- Create Unique Clustered Index with IGNORE_DUPE_KEY=ON to avoid duplicates
CREATE UNIQUE CLUSTERED INDEX [CIX_log_db_connections] ON log_db_connections
(
    [Database] ASC,
    Login ASC,
    Host ASC,
    Program ASC,
    LoginTime ASC
) WITH (IGNORE_DUP_KEY = ON)

INSERT [dbo].[log_db_connections]
(
[Database],
[Login],
[nt_user_name],
[LoginTime],
[Program],
[client_interface_name],
[Host],
[client_net_address],
[client_tcp_port],
[net_transport],
[net_packet_size],
[protocol_type],
[endpoint_id],
[encrypt_option],
[auth_scheme],
[num_reads],
[num_writes]
)
SELECT
DB_NAME(p.dbid) ,
s.login_name ,
nt_user_name,
s.login_time as LoginTime,
s.[program_name] as Program,
s.client_interface_name,
[host_name] as Host,
c.client_net_address,
c.client_tcp_port,
c.net_transport,
c.net_packet_size,
c.protocol_type,
c.endpoint_id,
c.encrypt_option,
c.auth_scheme,
c.num_reads,
c.num_writes
FROM sys.dm_exec_sessions s
inner join sys.sysprocesses p on
s.session_id = p.spid
inner join sys.dm_exec_connections c on
s.session_id = c.session_id
WHERE s.[program_name] not like 'Microsoft SQL Server Management Studio%'