Logging User Activity

I would like to log which users are running what queries on our SQL Server 2005. Is there a way to do this?

I have both domain users and SQL users using the server. In the Windows logs I can see when a domain user logs in, however, the SQL users are logged in as anonymous. Even so, I can't tell what the users are doing on the system.

I have several websites and other tools that use SQL logins. We did this so users wouldn't need to know a password. I would like to know which of those tools are being used. Since most tools use a specfic set of views, if I could measure the use of the users and those views, I would know which ones can be decommissioned.

If I should post this in another forum, please let me know. This seemed like the best place.

Rob

That is a task for SQL Profiler. If you are using Edition other than Express, you will have the Profiler tool. It should have been installed with SSMS and the other client tools.

Verify your Edition and we can give you more directed help.

|||

as Arnie said either you can use built in tool Profiler or you can use system SP to trace the system activities

Refer :

http://vyaskn.tripod.com/server_side_tracing_in_sql_server.htm

Madhu

|||

Arnie Rowland wrote:

That is a task for SQL Profiler. If you are using Edition other than Express, you will have the Profiler tool. It should have been installed with SSMS and the other client tools.

Verify your Edition and we can give you more directed help.

Enterprise edition, I believe. However, I've tried to verify and I'm not sure how to do that. I do know it is not the Express edition. I did find the SQL Profiler program.

Rob

|||

You can verify your Edition, Service Pack, etc. using:

SELECT @.@.VERSION

(or)

SELECT

SERVERPROPERTY( Edition ),

SERVERPROPERTY( ProductLevel )

Here are some resouces that will guide you with using SQL Profiler.

Profiler -How To: Use SQL Profiler
http://msdn2.microsoft.com/en-us/library/ms979207.aspx

Profiler -Introduction
http://www.developer.com/db/article.php/3482216

Profiler -SQL Profiler Tips and Tricks
http://www.developer.com/tech/article.php/3490086

Profiler - Step-By-Step: An introduction to SQL Server Profiler
http://articles.techrepublic.com.com/5100-6329_11-5054787.html

Profiler -Tracing SQL Statements
http://sqljunkies.com/Article/9513605D-FF3B-45AE-8056-ADF30772C1A9.scuk

Profiler -Use SQL Profiler to diagnose SQL Server performance issues
http://articles.techrepublic.com.com/5100-1035_11-1043670.html

Profiler -Using Profiler to Identify Poorly Performing Queries
http://www.sql-server-performance.com/sql_server_performance_audit10.asp

Profiler -Using SQL Profiler
2000 http://www.microsoft.com/technet/prodtechnol/sql/70/tips/sqlprof.mspx
2005 http://msdn2.microsoft.com/en-us/library/ms181091.aspx

|||

Arnie - thank you. I did some initial setup but can see I need to trim down the events being logged.

Rob