Auditing Database usage

Question

Auditing Database usage

winston Smith

SSCoach

Points: 19497
More actions
November 18, 2013 at 6:16 am

#279248

I have a request to audit database usage. Database usage is defined as anytime someone accesses a database for anything, read or write.
I have been looking at auditing but I can only use server level audits, as users can add or drop databases at any time and I cant enforce that they create a database audit in each database.
This is where the difficulty lies. I cant see any server level events that let me identify if a database was used.
Any ideas on how I might accomplish this?

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323504 More actions · Answer 1

winston, i think one of the built in extended events session templates, along with where you would add a filter on database Id, is probably what i'd look at first.

In 2012, if you Managmeent>>Extended Events and start up a new Session Wizard, one of the templates is "Query Detail Tracking";

from there, i just followed all the defaults until i got to filters, and coudl then add a filter for a specific databaseId.

would that be something you'd be able to work with?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

RTaylor2208 SSChampion Points: 13298 More actions · Answer 2

winston Smith (11/18/2013)
I have a request to audit database usage. Database usage is defined as anytime someone accesses a database for anything, read or write.
I have been looking at auditing but I can only use server level audits, as users can add or drop databases at any time and I cant enforce that they create a database audit in each database.
This is where the difficulty lies. I cant see any server level events that let me identify if a database was used.
Any ideas on how I might accomplish this?

Not that I have tried this but it may be worth looking inot adding a database audit to the model database that way every time a new database is created it would be included.

A few points on auditing though, how long do you need to store the audit data? as auditing generates a lot of data and if you are writing the audit to file be prepared to process a lot of data.

Writing to the memory caches can be done but if its a particularly busy database the data is only retained until the cache is full then it will start to overwrite.

MCITP SQL 2005, MCSA SQL 2012

winston Smith SSCoach Points: 19497 More actions · Answer 3

Thanks guys.

Im checking if ExtendedEvents could work for me.

Adding a DatabaseAudit to the model wont work as theres a good chance a lot of the databases will be restores from other servers.

Need to identify any databases that have not been "accessed" in a month (4 weeks) so without being clever with what I store, I need to keep the usage data for 4 weeks.

Keith Tate SSCrazy Eights Points: 9888 More actions · Answer 4

Strange question, but how accurate do you have to be? Can you simply use sp_WhoIsActive and capture the results, to a table, every x seconds/minutes? If you captured the results say every 30 seconds and then you could query the table to see the level of activity on each database.

Microsoft Certified Master - SQL Server 2008
Follow me on twitter: @keith_tate

Forum Etiquette: How to post data/code on a forum to get the best help[/url]

benjamin.reyes SSCertifiable Points: 5249 More actions · Answer 5

Sounds like a good use case for a logon trigger.

http://technet.microsoft.com/en-us/library/bb326598.aspx

Lowell SSC Guru Points: 323504 More actions · Answer 6

benjamin.reyes (11/18/2013)
Sounds like a good use case for a logon trigger.
http://technet.microsoft.com/en-us/library/bb326598.aspx

but a logon trigger would not tell what or when a database was accessed; it only fires at login, and not at each statement that gets executed.

it would only return the database context that was set as part of the connection string at the moment of loggin in.

I think winston is looking for the deeper details, like create database, select * from database..tablename ,update database..table , drop database, etc.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

winston Smith SSCoach Points: 19497 More actions · Answer 7

Login trigger wont work.

If a user logs on but then changes database context to another database, a login trigger wont capture that.

I need the most recent time a database was accessed for either a read or a write.

Extended events to a ring buffer looks like a potential solution, just playing with that now. will update once I have some further info.

benjamin.reyes SSCertifiable Points: 5249 More actions · Answer 8

If you just need to know if a database has been accessed and not anything specific about the users connection, you could probably just use sys.dm_db_index_usage_stats.

Though going back to the logon trigger you should be able to have it kick off a server side trace.