I've been asked before how many times a particular user has accessed objects in a database.

I solved it by creating a database audit specification to capture the SCHEMA_OBJECT_ACCESS_GROUP.

Here's the code I used

-- Setup the auditing

USE [master]

GO

CREATE SERVER AUDIT [User-Access]

TO FILE

(FILEPATH = N'F:\SQLdata\MSSQL10_50.A\MSSQL\Log' -- REPLACE WITH YOUR LOG PATH

,MAXSIZE = 0 MB

,MAX_ROLLOVER_FILES = 2147483647

,RESERVE_DISK_SPACE = OFF

)

WITH

(QUEUE_DELAY = 1000

,ON_FAILURE = CONTINUE

)

GO

ALTER SERVER AUDIT [User-Access] WITH (STATE = ON)

GO

USE [YOUR-DATABASE]

GO

CREATE DATABASE AUDIT SPECIFICATION [User-Access]

FOR SERVER AUDIT [User-Access]

ADD (SCHEMA_OBJECT_ACCESS_GROUP)

GO

ALTER DATABASE AUDIT SPECIFICATION [User-Access] WITH (STATE = ON)

GO

-- Let it run for a bit

-- Now stop the capture

USE [YOUR-DATABASE];

GO

ALTER DATABASE AUDIT SPECIFICATION [User-Access] WITH (STATE = OFF)

USE master;

GO

ALTER SERVER AUDIT [User-Access] WITH (STATE = OFF)

GO

-- Get the results

SELECT schema_name, object_name, server_principal_name, count(*)

FROM fn_get_audit_file('F:\SQLdata\MSSQL10_50.A\MSSQL\Log\User-Access*', default, default) -- REPLACE WITH YOUR LOG PATH

WHERE server_principal_name = 'THE-LOGIN-YOU-ARE-INTERESTED-IN'

AND schema_name NOT IN ('sys')

GROUP BY schema_name, object_name, server_principal_name

ORDER BY schema_name, object_name, server_principal_name

GO

Hope this helps.