We have a SQL Login account on a 2008 SQL Server, and no one seems to know if the account is actually being used or not. It is assumed it is being used, as it is configured in a Linked_Server connection, however no one knows if it is still really being used, and if so for what. How do we verify, aside from disabling and waiting for a squeal?
There are several options you can choose from. One determining factor might be how/where you want to store the login data. - If you are comfortable with Windows logs and are able to restart the SQL instance, the simplest method might be to enable login auditing for both successful and failed logins. Once that is done (and you restart the service), you will receive the appropriate messages in the Windows Application log with the source name matching the name of your Windows service for the SQL Server instance. - Another option is to use SQL Server Audit to store the information in an audit file. This option can also be configured to use the Windows Application or Security log. - If you are more comfortable with SQL table queries, you could use a login trigger to insert a row into a table whenever someone logs in. Be careful with this method because a bad login trigger can potentially block all logins until resolved by an admin.