locked
How to find unused logins in SQL Server 2008? RRS feed

  • Question

  • Hi,

    Is there any way to find unused logins in sql server 2008.

    Regards,

    Siva


    SivaG:Please Marked as Answered, if it resolves your issue.

    Thursday, July 18, 2013 11:59 AM

All replies

  • Refer to this post for more information. http://www.sqlservercentral.com/Forums/Topic609635-359-1.aspx You should have a time limit of unused logins and from there you can proceed based on the logic specified.

    Login auditing: Refer http://msdn.microsoft.com/en-us/library/ms175850.aspx

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

    Venkataraman R Be Blessed by Divine


    • Edited by Venkataraman R Thursday, July 18, 2013 12:23 PM additional information
    Thursday, July 18, 2013 12:20 PM
  • We have no direct method to find this.  A proper auditing system may tell you the login details as per Venkat suggestion.

    Srinivasan

    Thursday, July 18, 2013 12:27 PM
  • It would be good if you create Logon trigger see this article and dump the records in particular table.Then run select on that record to fetch information.


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    Thursday, July 18, 2013 12:29 PM
  • Here there is not direct method to get the unused logins, as per venkatsh and Shanky,through logon trigger you can find the unused logins.
    Thursday, July 18, 2013 12:33 PM
  • Here there is not direct method to get the unused logins, as per venkatsh and Shanky,through logon trigger you can find the unused logins.

    About direct method not sure but i would like Siva to use this query this will not give complete picture but will provide tentative dates for account created and accessed for all logins,still logon trigger method is best

    Use master

    go

    select createdate,updatedate,accdate,name from sys.syslogins



    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    Thursday, July 18, 2013 12:41 PM
  • You can also check defaut trace files that might help you too. Also, data present in it would be from last sql server restart.

    Manu Jaidka

    Thursday, July 18, 2013 12:53 PM
  • Hi Siva,

    First, I want to confirm what you mean by “unused logins”.

    If you want to find logins who connects to SQL Server currently, we can run “sp_who2” command.Please look up the " status "column.

    If you want to find the database users who have no mapped login account, we can refer to the following document:

    1.sp_who (Transact-SQL)
    http://msdn.microsoft.com/en-us/library/ms174313.aspx

    2.Troubleshoot Orphaned Users (SQL Server)
    http://msdn.microsoft.com/en-us/library/ms175475.aspx
       
    Regards,
    Heidi Duan

    Friday, July 19, 2013 7:27 AM