Post reply

t-sql to return logged in users for the week

  • February 20, 2007 at 7:29 pm

    #369604

    Hello,

    I am new to sql so please bare with me.. I would like to find a way to run a select query and get the amount of unique users that have logged into a particular database. We are using asp.net forms authentication and have another sql database to store users/passwords. There is a column 0/1 which indicated if a user is logged on.

    The problem is I need to run a report at the end of the week to show all unique users that have logged into a database. How would I go about doing this??

    Thanks

  • February 20, 2007 at 7:41 pm

    #690794

    Hi Andrew,

    I'm a Newbie also.  I would think that you would have to get that information from the log file for the particular database you want.  Another method would be to create a new table and add a trigger to add the user logging into the database.  There are probable better ways. 

    Hope this helps and someone comes up with a better way for you.

    Wes

  • February 20, 2007 at 7:52 pm

    #690797

    Yes I was trying to think how you could do it without having this value stored somewhere. I think you are right in that there has to be another column added to hold a session date or something that can retain this value.

    I did not design this database and just inherited this today... I will assume that the 0 | 1 value is only held during the active session and then goes back to 0. ( of course I will have to double check) This obviously is no good for what I need.

    Thanks for your reply though

  • February 21, 2007 at 6:44 am

    #690881

    SQL does not keep any form of log of logins, unless either the option to log successful logins to the error log is checked, or the server has c2 auditing switched on, or profiler is running capturing login attempts.

    SQL 2005 sp2 has a login DDL trigger that could be used to keep track of logins, but probably the easiest way would be for your front end app to log the username and datetime of the login attempts.

    Unless your app does this already, or you have one of the options I mentioned on, there's no way for you to get that data.

    Maybe speak to the person who requested that report, ask him if he has received that report in the past (indicating that the data is stored somewhere) or if it's a new request.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply