As I mentioned in the introductory post, during the Introduction to SQL Server Security session for Pragmatic Work’s Training on the T’s, I received a large number of questions that there wasn’t time to answer.  Instead of just a re-cap of all of the questions, instead I’ve opted to put together a post per topic.  Hopefully, this will help, not just those that attended the session, but also anyone searching for the same questions later on.

Security question

The next question in the list is:

How can i be sure a user is no longer used, so it can be deleted? Or when was the last time the logon was used?

This is an interesting question and one that takes some preparation in order to gather the information needed.  By default, there is nothing configured within SQL Server that tracks when a logon or user was last used.  The question, itself, is asking for the absence of activity, which can be difficult to track directly.  Indirectly, though, we can track the last time that a logon was used and leverage that information, over time, to find logons that are no longer being used.

There are three main ways that this information can be gathered.  Each has it’s own pros and cons.  It will be up to you to determine which option is best.  The options are:

• Login Audting: Leverage the SQL Server configuration option to capture Successful logins only or Both failed and successful logins.  Once configured, every login will be captured in the SQL Server event log.  This is easy to setup, but in an active server would easily make the SQL Server event logs challenging, at best, to use.  Also, the information would need to be queried from the event logs in order to make it useful.
• SQL Server Audit: Nearly as easy as Login Auditing, but with a little but more setup.  SQL Server Audits can be configured to track successful logins and stores the information into a event log dedicated to the audit.  This doesn’t overburden the SQL Server event log and consolidates the activity down to a small set of files.  The downside is that the information is still gathered at the per login event.  Before the data can be used, it would need to be aggregated to identify which logins are being used and then which aren’t.
• Event Notifications: This method leverages service broker to capture login activity and queue it for later processing.  Through an activation stored procedure the queue rows can be processed and aggregated into a reporting table.  The great thing about this option is that the information can be gathered and aggregated over time as the logins occur.  When the analysis is needed, the results are already prepared.  The downside is that if there are many logins occurring, it is possible for the queue and processing to fall behind.  You need to balance the need to gather information with the resources that gathering it will consume.

Summary

There are undoubtedly other ways that login information.  In order to answer the question posed, though, you have to select one of these options and start collecting information.  Over time, the logins that are used will be logged and when enough time passes the unused logins can be dropped. Out of all of these solutions, my preferred is the event notifications.  Once configured it works well at gathering the information over time.  What would you do to solve this security problem?