Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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?

share|improve this question
add comment

3 Answers

up vote 1 down vote accepted

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.

share|improve this answer
    
Profiler won't give you the IP Address, but it will give you the hostname of the offending user. –  mrdenny Aug 5 '09 at 20:18
add comment

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.
share|improve this answer
    
Splitting the task into two is a good idea. I'll first determine what clients are connecting to the server and give them each unique logins, then I'll set their permissions. –  Jeremy Stein Aug 10 '09 at 20:38
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.