How to find, when the login last used on SQL Server?

  • sivaprasad

    SSCertifiable

    Points: 6183

    I am performing audit of SQL Server Instances in our environment. I like to know how to find out the details of below. It applies to SQL Server version 2000, 2005 and 2008

    How to find the SQL Server Instance Last used?

    There are SQL Server Instances on stopped state.

    How to find, when the user database last used?

    It is to make judjement if the database is in use or can be dropped

    How to find, when the login last used on SQL Server?It is to make decision of if the login is not in use for given period of time, to drop the login.

    I would appreciate any inputs on this. As there 'n' number of servers on the environment, i have to automate this process.

    Sivaprasad S - [ SIVA ][/url]http://sivasql.blogspot.com/[/url]

  • SQLRNNR

    SSC Guru

    Points: 281243

    sivaprasad (4/1/2010)


    I am performing audit of SQL Server Instances in our environment. I like to know how to find out the details of below. It applies to SQL Server version 2000, 2005 and 2008

    How to find the SQL Server Instance Last used?

    There are SQL Server Instances on stopped state.

    How to find, when the user database last used?

    It is to make judjement if the database is in use or can be dropped

    How to find, when the login last used on SQL Server?It is to make decision of if the login is not in use for given period of time, to drop the login.

    I would appreciate any inputs on this. As there 'n' number of servers on the environment, i have to automate this process.

    Instance and Database Last used: You will need to check for records in the database and find the most recent insert or update made.

    Find the last login: Auditing. If the database is active, setup auditing now.

    Alternatively, check app code and see if there are any connection strings that connect to that database.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • Bhuvnesh

    SSC Guru

    Points: 59344

    You can also choose C2 Audit Trace , it is good option but the main limitation of the auditing is that it reduces the performance of the SQL Server. This happens due to saving the every action to the file. Second limitation is the hard disk space. These auditing files grow rapidly, which will reduce the disk space. According to the C2 , if it is not able to write to the trace file, SQL Server will be shutdown.

    See this link http://www.sqlservercentral.com/articles/Monitoring/basicsofc2auditing/1547/

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

  • K. Brian Kelley

    SSC Guru

    Points: 114486

    I would not recommend C2 audit trace unless you have a regulatory requirement for it. You can create a much more lightweight server side trace to audit logins. Or better yet, you can turn on login auditing at the server level and the info will be written to both the event log (application event log on the OS) and the SQL Server error log.

    K. Brian Kelley
    @kbriankelley

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 720347

    If you suspect that a login hasn't been used in a long time, or is inactive, I would disable it, make a note in your logs, and then notify help desk people of the change. You can easily enable it again later.

  • SQLRNNR

    SSC Guru

    Points: 281243

    Steve Jones - Editor (4/9/2010)


    If you suspect that a login hasn't been used in a long time, or is inactive, I would disable it, make a note in your logs, and then notify help desk people of the change. You can easily enable it again later.

    Quite frankly that is a very efficient way to find if a login is used or not.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 720347

    As long as you can respond quickly enough. I'd try the trace for a week first and then disable others.

    Note that I would not delete any old logins for a year. I have seen plenty of situations where a particular login or piece of code was used once a year.

  • Bhuvnesh

    SSC Guru

    Points: 59344

    K. Brian Kelley (4/9/2010)


    you can turn on login auditing at the server level and the info will be written to both the event log

    Can you post any link for it ?

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 720347

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

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