2

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.

Improve this question
4
  • 3
    Only unsuccessful login attempts will be logged in by default. See dba.stackexchange.com/questions/82347/…
    – Aaron Bertrand
    Commented Nov 12, 2014 at 19:42
  • You could do it with a trace or extended events. You could also build something around this view sys.dm_exec_sessions or parse the event logs. Just have to decide how complex you want to make this
    – Jeff A
    Commented Nov 12, 2014 at 20:04
  • Granted that would be just forward looking not looking at what's happened over the last week.
    – Jeff A
    Commented Nov 12, 2014 at 20:11
  • this may be useful to you sqlservercentral.com/articles/SQL+Server+2005/64547
    – mounaim
    Commented Nov 12, 2014 at 21:06

1 Answer 1

Reset to default
2

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

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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