question

Cleonlake avatar image
Cleonlake asked

How to verify if a SQL Account is still used?

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?
sql serversecurityloginauditauthentication
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Server side trace is a lightweight way to audit logins. You do not need Enterprise ED (auditing) or triggers. Just run a trace and then import it to a table. Then just hit the table with some t-sql.
0 Likes 0 ·

1 Answer

·
Tom Staab avatar image
Tom Staab answered
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.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.