0

I have a question, I hope you can give me some guidance based on your high expertise level.

There is an audit requirement where we have to provide login information per databases on a given server. Basically, I’m talking about DB users that authenticate externally to a DB (containment type is None).

How should I proceed to capture this event to answer the following question? “Give me the last login for the DB User XYZ authenticated in DB ABC?”

I couldn’t find anything in SQL Server Audit Specification (SQL 2012). It only works when the DB has the containment type set to Partial. But I also need to capture logins for SQL 2005 and 2008 databases.

Your help is greatly appreciated, Thank you in advance.

Improve this question
1
  • Why not setting up a server level audit with SUCCESSFUL_LOGIN_GROUP and FAILED_LOGIN_GROUP ? If users are mapp through the instance to an unique database, you can surely give the last login connected to it.
    – Mickael
    Oct 19, 2016 at 15:10

1 Answer 1

Reset to default
0

For this I would default to extended events for the 2008 server and up. There is a good blog post about it here.

Additionally, for the 2005 server, you would need to use a trace as the extended event functionality wasn't available. There was another post for this shown here.

Both of these options give you different ways of storing and what to capture such as login name, time, database name, Etc.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.