asked
Audit Requirement - Capture Logins vs Database
Hi Team, 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.
There are various options for this, for example: - At instance level change login auditing to "both failed and succesful logins" (right click instance->properties->security). This logs all logins to the SQL errorlog. So a drawback is a fast growing logfile. Note: you'll have to restart the instance for this. - create a login trigger, see [link text][1] [1]:
https://www.simple-talk.com/sql/t-sql-programming/logon-triggers/
20 People are following this question.
