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.
Best Answer
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.