Audit SQL Server 2005 when accessed by multiple clients as sa

Question

We have a SQL Server 2005 database that is used by various programs developed over the years. Most of these programs access the database using the sa login. My job is to figure out what system uses the database for what purpose so we can start creating logins with appropriate permissions or otherwise manage access.

I want to know, for a given connection, what IP logged in and what statements were executed. How can I get this information, and can I do it without significantly slowing the database server?

Answer 1

Yes, im fairly certain that the SQL profiler tool that comes with full version of SQL 2005 will enable tracing of client IP address of that traffic. If you are using SQL Express, then get this: http://sqlprofiler.googlepages.com . Edit the profile trace so that it logs bare minimum information and then trace for whatever time period is needed and you should be able to figure it all out.

Answer 2

Since you're using SQL2005, you can use a LOGON trigger to capture when a process logs in using sa. The EVENTDATA that is available in the trigger gives you quite a bit of information about the connection, including the host name or IP of the connecting client.

From there, you can create a server trace to find out what the session is doing. You may be able to do that from within the trigger - I can't find anything that says you can't, but I haven't tried it myself, either.

Answer 3

I don't know of a way to do this based on the IP address.

One way would do it is to:

• Start with creating the user logins, give them the same rights as sa (they are using sa now anyway).
• Log what each user does using profiler. (There is some performance hit)
• Reduce the rights of the user logins to what is required.

The other way to do it is:

• Start with creating the user logins with no rights
• Run the apps with the user and see which access denied error you get
• Give the rights of the user logins as required.