SQL Server 2008 R2 – How to Get All Logged In Users for This Week

sql serversql-server-2008-r2

I'm using SQL Server 2008R2 and I setup a testing database that I need to know who tried logging in so far. Is there a stored procedure or a View or anything that I can gather these type of information for this week or any specific day?

This is the same server that also has the production database, not sure if this will matter with performance issues as I'm reading online that Traces could affect?

Thanks for any advice.

Best Answer

You have a few options, some of which were already pointed out.

First, though, if these events haven't been captured so far there is no "historic" way of obtaining the data. There may be ways to get some of the data but chances are you won't get all or even most of it.

Going forward, however, it seems you only want those who successfully connect to the instance. This can be complicated if something such as connection pooling with a sql login, but I'll leave that as something for you to decide on how you want to track it back to the users.

  1. Server Side Trace

Even though traces are deprecated they still work well. If it's just a successful login needed then this can be fairly low overhead. It has well known functions to load into tables and do your analysis.

http://msdn.microsoft.com/en-us/library/cc293613.aspx

  1. Extended Events

Since you only need the successful logins, there is an event "sqlserver.login" that can work well for you. Send it to an event_file target and it's fairly simple as well. There are also functions to load the files and do your analysis.

http://technet.microsoft.com/en-us/library/bb630317(v=sql.105).aspx

  1. Logon Triggers

Probably my least favorite option but here for completeness. The logins could be logged to a table, but why re-invent the wheel? Trace and extended events will do this for you.

http://msdn.microsoft.com/en-us/library/bb326598.aspx

  1. SQL Audit

I didn't bunch this with extended events, even though in reality it leverages the same framework. This is an enterprise only feature, and while it can do this and is targeted for items such as this... I feel most aren't running enterprise edition.

http://msdn.microsoft.com/en-us/library/cc280386.aspx

Related Question