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

Logging user activity

I want to find out how to turn/configure logging so I can "see" who makes
changes to the databases. Thanks
Run profiler all the time and log to a table or use a product like
Lumigent's Entegra.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Patrick R." <PatrickR@.discussions.microsoft.com> wrote in message
news:CBA06229-DC30-4773-A460-831ABB1115E2@.microsoft.com...
>I want to find out how to turn/configure logging so I can "see" who makes
> changes to the databases. Thanks
|||Patrick R. wrote:
> I want to find out how to turn/configure logging so I can "see" who makes
> changes to the databases. Thanks
If you want to "see" who makes changes to the database, you'll have to
create an audit mechanism on the tables that you want to track. See
http://www.nigelrivett.net/AuditTrailTrigger.html
Tracy McKibben
MCDBA
http://www.realsqlguy.com

Logging user activity

I want to find out how to turn/configure logging so I can "see" who makes
changes to the databases. ThanksRun profiler all the time and log to a table or use a product like
Lumigent's Entegra.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Patrick R." <PatrickR@.discussions.microsoft.com> wrote in message
news:CBA06229-DC30-4773-A460-831ABB1115E2@.microsoft.com...
>I want to find out how to turn/configure logging so I can "see" who makes
> changes to the databases. Thanks|||Patrick R. wrote:
> I want to find out how to turn/configure logging so I can "see" who makes
> changes to the databases. Thanks
If you want to "see" who makes changes to the database, you'll have to
create an audit mechanism on the tables that you want to track. See
http://www.nigelrivett.net/AuditTrailTrigger.html
Tracy McKibben
MCDBA
http://www.realsqlguy.com

Logging user activity

I want to find out how to turn/configure logging so I can "see" who makes
changes to the databases. ThanksRun profiler all the time and log to a table or use a product like
Lumigent's Entegra.
--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Patrick R." <PatrickR@.discussions.microsoft.com> wrote in message
news:CBA06229-DC30-4773-A460-831ABB1115E2@.microsoft.com...
>I want to find out how to turn/configure logging so I can "see" who makes
> changes to the databases. Thanks|||Patrick R. wrote:
> I want to find out how to turn/configure logging so I can "see" who makes
> changes to the databases. Thanks
If you want to "see" who makes changes to the database, you'll have to
create an audit mechanism on the tables that you want to track. See
http://www.nigelrivett.net/AuditTrailTrigger.html
Tracy McKibben
MCDBA
http://www.realsqlguy.com

Logging table activity

Hi peeps,

We have a great big database (90gb) which has been populated (monopolised) by our finance team, and its full of tables that probably aren't being used at all. But know knows whats being used and what isn't or they don't have the time to go through it with me.

So I have decided to implement a procedure that logs table activity on this database, and if for example a table isn't used for a month then it will be archived off and zipped up.

I have a few ideas in my head how I can acheive this, but I am looking for some opinions and ideas from you guys?

Thanks in advancetriggers everywhere.|||Triggers won't do much for reporting

I'd say you need to use Profiler

logging of activity on database

I want to make an investigation on the activity of the sql-server. This
means not only logins , but I want to know also the sql-commands which are
done. Is this already logged and captured in a database (in msdb for
example).
A second question: can i also see the CPU-activity of the computer where the
sql-server is put on. I have full connection to the sql-server, but i do not
have administration permission to that computer.
Thank you.
Johan
Use SQL Server Profiler for this purpose
http://www.sql-server-performance.co...filer_tips.asp
"johan Goris" <johangoris@.rewah.com> wrote in message
news:40d155cc$0$26690$a0ced6e1@.news.skynet.be...
> I want to make an investigation on the activity of the sql-server. This
> means not only logins , but I want to know also the sql-commands which are
> done. Is this already logged and captured in a database (in msdb for
> example).
> A second question: can i also see the CPU-activity of the computer where
the
> sql-server is put on. I have full connection to the sql-server, but i do
not
> have administration permission to that computer.
> Thank you.
>
|||Unfortunately for SQL 2000, you need the sysadmin role to run Profiler,
though I understand SQL 2005 would not need that. If you want to see the
CPU activity, the best you can do is SELECT @.@.CPU_BUSY. For SQL related
counters, you can use SELECT * FROM master..sysperfinfo
You could set up Performance Monitor on your server to log counters to a
table on your database, though I think it's only available on WinXP and
Win2003 (someone correct me here). Alternatively, I use a custom built tool
to collect and store local and remote performance counters from Win2000
servers to a SQL Server table.
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backup files? Try MiniSQLBackup
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uq3zLjEVEHA.3512@.TK2MSFTNGP12.phx.gbl...[vbcol=seagreen]
> Johan
> Use SQL Server Profiler for this purpose
> http://www.sql-server-performance.co...filer_tips.asp
>
>
>
> "johan Goris" <johangoris@.rewah.com> wrote in message
> news:40d155cc$0$26690$a0ced6e1@.news.skynet.be...
are
> the
> not
>
|||Peter Yeoh wrote:
> Unfortunately for SQL 2000, you need the sysadmin role to run Profiler,
> though I understand SQL 2005 would not need that.
i don't like that. i hope there's a way to prevent people that aren't
SAs from running profiler.
|||Perhaps I misphrased my statement regarding SQL2005. What I meant to say
was that to run Profiler in SQL2005, you still need to assign rights, but
not the sysadmin rights. It's actually an improvement then.
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backups? Try MiniSQLBackup
"ch" <ch@.dontemailme.com> wrote in message
news:40D19EF9.5B69D37E@.dontemailme.com...
> Peter Yeoh wrote:
> i don't like that. i hope there's a way to prevent people that aren't
> SAs from running profiler.

logging of activity on database

I want to make an investigation on the activity of the sql-server. This
means not only logins , but I want to know also the sql-commands which are
done. Is this already logged and captured in a database (in msdb for
example).
A second question: can i also see the CPU-activity of the computer where the
sql-server is put on. I have full connection to the sql-server, but i do not
have administration permission to that computer.
Thank you.Johan
Use SQL Server Profiler for this purpose
http://www.sql-server-performance.c...ofiler_tips.asp
"johan Goris" <johangoris@.rewah.com> wrote in message
news:40d155cc$0$26690$a0ced6e1@.news.skynet.be...
> I want to make an investigation on the activity of the sql-server. This
> means not only logins , but I want to know also the sql-commands which are
> done. Is this already logged and captured in a database (in msdb for
> example).
> A second question: can i also see the CPU-activity of the computer where
the
> sql-server is put on. I have full connection to the sql-server, but i do
not
> have administration permission to that computer.
> Thank you.
>|||Unfortunately for SQL 2000, you need the sysadmin role to run Profiler,
though I understand SQL 2005 would not need that. If you want to see the
CPU activity, the best you can do is SELECT @.@.CPU_BUSY. For SQL related
counters, you can use SELECT * FROM master..sysperfinfo
You could set up Performance Monitor on your server to log counters to a
table on your database, though I think it's only available on WinXP and
Win2003 (someone correct me here). Alternatively, I use a custom built tool
to collect and store local and remote performance counters from Win2000
servers to a SQL Server table.
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backup files? Try MiniSQLBackup
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uq3zLjEVEHA.3512@.TK2MSFTNGP12.phx.gbl...
> Johan
> Use SQL Server Profiler for this purpose
> http://www.sql-server-performance.c...ofiler_tips.asp
>
>
>
> "johan Goris" <johangoris@.rewah.com> wrote in message
> news:40d155cc$0$26690$a0ced6e1@.news.skynet.be...
are[vbcol=seagreen]
> the
> not
>|||Peter Yeoh wrote:
> Unfortunately for SQL 2000, you need the sysadmin role to run Profiler,
> though I understand SQL 2005 would not need that.
i don't like that. i hope there's a way to prevent people that aren't
SAs from running profiler.|||Perhaps I misphrased my statement regarding SQL2005. What I meant to say
was that to run Profiler in SQL2005, you still need to assign rights, but
not the sysadmin rights. It's actually an improvement then.
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backups? Try MiniSQLBackup
"ch" <ch@.dontemailme.com> wrote in message
news:40D19EF9.5B69D37E@.dontemailme.com...
> Peter Yeoh wrote:
> i don't like that. i hope there's a way to prevent people that aren't
> SAs from running profiler.

logging of activity on database

I want to make an investigation on the activity of the sql-server. This
means not only logins , but I want to know also the sql-commands which are
done. Is this already logged and captured in a database (in msdb for
example).
A second question: can i also see the CPU-activity of the computer where the
sql-server is put on. I have full connection to the sql-server, but i do not
have administration permission to that computer.
Thank you.Johan
Use SQL Server Profiler for this purpose
http://www.sql-server-performance.com/sql_server_profiler_tips.asp
"johan Goris" <johangoris@.rewah.com> wrote in message
news:40d155cc$0$26690$a0ced6e1@.news.skynet.be...
> I want to make an investigation on the activity of the sql-server. This
> means not only logins , but I want to know also the sql-commands which are
> done. Is this already logged and captured in a database (in msdb for
> example).
> A second question: can i also see the CPU-activity of the computer where
the
> sql-server is put on. I have full connection to the sql-server, but i do
not
> have administration permission to that computer.
> Thank you.
>|||Unfortunately for SQL 2000, you need the sysadmin role to run Profiler,
though I understand SQL 2005 would not need that. If you want to see the
CPU activity, the best you can do is SELECT @.@.CPU_BUSY. For SQL related
counters, you can use SELECT * FROM master..sysperfinfo
You could set up Performance Monitor on your server to log counters to a
table on your database, though I think it's only available on WinXP and
Win2003 (someone correct me here). Alternatively, I use a custom built tool
to collect and store local and remote performance counters from Win2000
servers to a SQL Server table.
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backup files? Try MiniSQLBackup
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uq3zLjEVEHA.3512@.TK2MSFTNGP12.phx.gbl...
> Johan
> Use SQL Server Profiler for this purpose
> http://www.sql-server-performance.com/sql_server_profiler_tips.asp
>
>
>
> "johan Goris" <johangoris@.rewah.com> wrote in message
> news:40d155cc$0$26690$a0ced6e1@.news.skynet.be...
> > I want to make an investigation on the activity of the sql-server. This
> > means not only logins , but I want to know also the sql-commands which
are
> > done. Is this already logged and captured in a database (in msdb for
> > example).
> > A second question: can i also see the CPU-activity of the computer where
> the
> > sql-server is put on. I have full connection to the sql-server, but i do
> not
> > have administration permission to that computer.
> >
> > Thank you.
> >
> >
>|||Peter Yeoh wrote:
> Unfortunately for SQL 2000, you need the sysadmin role to run Profiler,
> though I understand SQL 2005 would not need that.
i don't like that. i hope there's a way to prevent people that aren't
SAs from running profiler.|||Perhaps I misphrased my statement regarding SQL2005. What I meant to say
was that to run Profiler in SQL2005, you still need to assign rights, but
not the sysadmin rights. It's actually an improvement then.
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backups? Try MiniSQLBackup
"ch" <ch@.dontemailme.com> wrote in message
news:40D19EF9.5B69D37E@.dontemailme.com...
> Peter Yeoh wrote:
> >
> > Unfortunately for SQL 2000, you need the sysadmin role to run Profiler,
> > though I understand SQL 2005 would not need that.
> i don't like that. i hope there's a way to prevent people that aren't
> SAs from running profiler.