Post reply

Checking comuter name that logged in?

  • June 12, 2009 at 2:11 am

    #205460

    Hiya,

    I have searched Google and here, but have not found anything concrete. Some user here use the same username and password to log into SQL Server. No best practice I know, but its the way this system was set up before I arrived. Cant really change the way user login right now....so need a work around in the interim.

    Since they use the same server login, does anyone know how I can check the computer name of all the logins made? Any help appreciated! 😉

  • June 12, 2009 at 2:22 am

    #1008434

    Could you explain a bit more why you need this information and especially when.

    Do you need it real-time or for auditing purposes?

    In activitymonitor and when executing sp_who2 you can see from which hosts the connections are coming.

    If you want to store the information in an audit table, you probably have to use Logon triggers or Event Notifications.

    [font="Verdana"]Markus Bohse[/font]

  • June 12, 2009 at 3:17 am

    #1008449

    Hi!

    Thanks for the quick reply. A table has missing information, and it seems someone updated information on the table and deleted information. The only way this could have happened....is via one on the users. As they all have the same username and password to log in, we cant see who. Their computer names are unique though...so if we can see the computer name of the logins...thats great.

    Would love to see a history for Audit purposes...but checked the DB properties...and only unsuccessful login option was selected in the security tab. So not sure it will have the details now. I selected it to audit all logins from now on.

    So to sum up...would love to see a previous history of logins (with computer names) and would love a history from now on. Real time + Audit. Is this possible?

  • June 12, 2009 at 3:44 am

    #1008470

    I think in that case your only option would be reading from the transaction log (backups) using a tool like APEX SQLLog. http://www.apexsql.com/sql_tools_log.asp.

    Of course that will only work if you don't use simple recovery model.

    [font="Verdana"]Markus Bohse[/font]

  • June 12, 2009 at 4:34 am

    #1008493

    Thank you! Will take a look 😀

  • June 12, 2009 at 4:48 am

    #1008502

    Select host_name() should give you the name of the workstation initiating the connection.

    Note... if this is a web application, then the connection will be initiated from the web server, so this is what you will end up with, not the name of the actual workstation.

  • June 12, 2009 at 6:33 am

    #1008558

    Thanks, that works. How to get that info somewhere when a user logs in to the sql server management console is what Im looking for. Any ideas?

  • June 12, 2009 at 6:39 am

    #1008563

    How to get that info somewhere when a user logs in to the sql server management console is what Im looking for.

    Look at SQL Profiler and the Login Event

  • June 12, 2009 at 6:47 am

    #1008566

    Kewl, will take a look, thanks for the info!

  • June 12, 2009 at 7:05 am

    #1008586

    Found the HostName in SQL Profile, trace running on user pc's only. So going forward this is great, pitty can get a history cos I know the date we lost the data....would love to have seen who was logged on at that time. Anyways....should this data go missing again in future....at least the trace thats running will give us the info!

    Thanks for the help 😉

Viewing 10 posts - 1 through 9 (of 9 total)

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