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.
Best Answer
Queries aren't logged by SQL Server, unless they meet some criteria that puts them into the default trace or system_health Extended Events session (some details on what they collect here).
If you can control data access via stored procedures, you can easily add your own logging to those. But that doesn't sound like the case. Otherwise you will have to rely on triggers (for DML, but not SELECT), server-side trace, extended events, auditing, or 3rd party monitoring tools.