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

Question

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

sivaprasad

SSCertifiable

Points: 6183
More actions
April 1, 2010 at 12:11 am

#222732

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]

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

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

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

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: 59351 More actions · Answer 2

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: 114642 More actions · Answer 3

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: 736478 More actions · Answer 4

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: 281334 More actions · Answer 5

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: 736478 More actions · Answer 6

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: 59351 More actions · Answer 7

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: 736478 More actions · Answer 8

You configure this in the server properties http://msdn.microsoft.com/en-us/library/ms188470%28SQL.90%29.aspx