1

SQL Server 2012 introduced a few new audit action groups. Two that I'm interested in using are to audit log-ons/offs at the database level:

SUCCESSFUL_DATABASE_AUTHENTICATION_GROUP
DATABASE_LOGOUT_GROUP

The problem is, I also have SQL Server 2008 R2 instances that I'd like to deploy this on. Are there alternatives to achieve this using native SQL Server Audit? Or do I have to do this by other means (eg. SQL Profiler Trace, Logon/logoff triggers)?

  • 1
    Does someone who connects to master and then runs a query that references a table in databaseX ever "log on" to databaseX? If that query references tables in databases Y and Z, do you expect log on events happen on all three databases? If a user has a default database of X they'll always log on there but then may only work in other databases. Outside of contained databases and Azure SQL Database, I feel like logging on is something that happens at the instance level, not the database level, and if the log on only happens for the original db it doesn't hold a lot of value for auditing purposes. – Aaron Bertrand Oct 25 '17 at 15:15
  • @Aaron Bertrand: Thanks! To answer your 1st question: sometimes they do, especially if it's an ad-hoc query being executed in SSMS & they change the db context manually, no? I get what you're saying how it doesn't hold a lot of value but would like to capture 'all' log on events, such as the scenario mentioned above, for auditing purposes. – Chinesinho Oct 26 '17 at 19:00
1

SQL Server 2012 introduced database-level logon/logoff audit tracking as part of the "contained databases" feature, which allows SQL Server logins to be defined at the database level. In prior versions of SQL Server there is no such concept of login or logout at the database level.

From the Microsoft Docs:

SUCCESSFUL_DATABASE_AUTHENTICATION_GROUP
Indicates that a principal successfully logged in to a contained database. Equivalent to the Audit Successful Database Authentication Event Class.

DATABASE_LOGOUT_GROUP
This event is raised when a contained database user logs out of a database. Equivalent to the Audit Database Logout Event Class.

Be aware that unless you are using contained databases, and connections are being made to those databases via contained logins, those audits will not reflect login/logout events.

SQL Server Audit on 2008 R2 includes the ability to audit successful and failed logins at the server level via:

SUCCESSFUL_LOGIN_GROUP
FAILED_LOGIN_GROUP

Alternatively, you can configure "Login Auditing" on the "Security" tab of the "Server Properties" dialog.

In SSMS, right-click the server name in the "Object Explorer" panel. Then choose "Properties" from the list. Select the "Security" page, then specify "Both failed and successful logins" from the "Login auditing" section.

enter image description here

Login audit entries are written to the SQL Server Error Log; you can view them with this command:

EXEC sys.xp_readerrorlog 0, 1, 'Login';

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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