locked
How to find unused logins? RRS feed

  • Question

  • Hi All,

    We have one urgent requirment.How to find unused logins in SQL server 2008? Is there any script to remove unused logins?

    Please help me to complete the task.

    Thanks,

    Siva G


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

    Tuesday, March 4, 2014 2:45 PM

Answers

  • Siva,

    There is no way to find unused logins. 

    You need to place a logon trigger to gather session details and query it later

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

    OR

    schedule a job to gather sysprocesses active session details in a table

    -Prashanth

    • Marked as answer by SivaReddyG Wednesday, March 5, 2014 11:54 AM
    Tuesday, March 4, 2014 3:14 PM
  • Hello Siva,

    How do you define "unused logins", such who have never logged in or ...? Anyway, by default login activities are not logged by SQL Server, you have to implement a logging on your own. Even LOGINPROPERTY (Transact-SQL) don't return such information.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    • Proposed as answer by Prashanth Jayaram Tuesday, March 4, 2014 3:11 PM
    • Marked as answer by SivaReddyG Wednesday, March 5, 2014 11:54 AM
    Tuesday, March 4, 2014 2:54 PM

All replies

  • Hello Siva,

    How do you define "unused logins", such who have never logged in or ...? Anyway, by default login activities are not logged by SQL Server, you have to implement a logging on your own. Even LOGINPROPERTY (Transact-SQL) don't return such information.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    • Proposed as answer by Prashanth Jayaram Tuesday, March 4, 2014 3:11 PM
    • Marked as answer by SivaReddyG Wednesday, March 5, 2014 11:54 AM
    Tuesday, March 4, 2014 2:54 PM
  • Siva,

    There is no way to find unused logins. 

    You need to place a logon trigger to gather session details and query it later

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

    OR

    schedule a job to gather sysprocesses active session details in a table

    -Prashanth

    • Marked as answer by SivaReddyG Wednesday, March 5, 2014 11:54 AM
    Tuesday, March 4, 2014 3:14 PM