How do I track logins, connections, user accounts, queries, ...?

I am a BI person who has been asked to step in and pretend I am a DBA. I am not a novice with databases but AM a novice at the admin type stuff.

We have a SQL Server database that is going to be decommissioned. I have been asked to determine who is using the server, the databases that are being used and the queries being run.

So far I've created a table to track server logins as well as check number of connections as referred to @ http://www.mssqltips.com/sqlservertip/3171/identify-sql-server-databases-that-are-no-longer-in-use/

However, once I had that info I was asked to track:

• The user account or credentials being used to connect to the server

• the database(s) being queried

• The IP address or DNS name from which the connection was made

• The SQL being used

They want to know specifically who is using any databases on the server so they can determine which need to be migrated and who they need to contact as the database is migrated.

Any help is GREATLY appreciated!

I would suggest you to run a server side trace - for your entire business life cycle. There might be cases where some reports are ran end of month or end of quarter.

So you will need below event in a server side trace 11,13,16,19,33,37,61,162 with these columns 1,6,8,10,11,12,14,27,35,40.

You can filter out stuff that you dont need using sp_trace_setevent (note that it is announced deprecated).

Alternatively you can use Extended events - Tracking SQL Server Database Usage.

Note: Currently in my environment, I am using Server side trace and its a very low impact. Also, I have a job that dumps the profiler info into a table for analysis.

You can have below table structure for loading trace data :

CREATE TABLE [dbo].[login_trace](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [ServerName] [varchar](300) NULL,
    [HostName] [varchar](300) NULL,
    [ApplicationName] [varchar](300) NULL,
    [DatabaseName] [varchar](200) NULL,
    [LoginName] [varchar](100) NULL,
    [StartTime] [datetime] NULL
) ON [PRIMARY]

You can adjust it as per your requirement.

StackExchange Database Administrators Q#77952, answer score: 6