Database user activity

Question

Post reply

Database user activity

pallavi.unde

SSCommitted

Points: 1987
More actions
December 7, 2011 at 2:48 am

#391057

I want to keep a track on database user activity in MSSQL 2005.
the script should return the username,activity done like alter,delete database
alter delete table,views,storeprocedure. . . ???
which user running which query.....
i mean to say all the activity of user should get tracked in MSSQL2005.
pls reply
Thanks & Regards,
Pallavi

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Devendrakumar SSC-Forever Points: 42493 More actions · Answer 1

It’s just for viewing or logging it for future as well (Audit)?

spaghettidba SSC Guru Points: 105732 More actions · Answer 2

If you want to record all the statements run by users, you have only one option, which is setting up a trace.

Be aware that it will likely slow down the server and take lots of disk space.

-- Gianluca Sartori

pallavi.unde SSCommitted Points: 1987 More actions · Answer 3

Can you please guide me about creating trace..

please tell me the steps of creating trace...

Thanks & Regards,
Pallavi

spaghettidba SSC Guru Points: 105732 More actions · Answer 4

Google is your friend:

http://www.google.com/search?q=create+trace+sql+server

-- Gianluca Sartori

pallavi.unde SSCommitted Points: 1987 More actions · Answer 5

in that i am not able to understand the first step :-

On the File menu, click New Trace, and connect to an instance of SQL Server.

in my MSSQL there is no New Trace tab :w00t:

Thanks & Regards,
Pallavi

Devendrakumar SSC-Forever Points: 42493 More actions · Answer 6

pallavi.unde (12/7/2011)
in that i am not able to understand the first step :-
On the File menu, click New Trace, and connect to an instance of SQL Server.
in my MSSQL there is no New Trace tab :w00t:

Surely, it's not there in SSMS. The article is on 'SQL Server Profiler'. It's another utility in SQL Server.

Grant Fritchey SSC Guru Points: 398886 More actions · Answer 7

Another option to pursue, and a good plan for your long term skill set development, is to use Extended Events to capture all user activity. It works a lot like Trace/Profiler, but it causes much less impact on your system. Here's an introduction[/url].

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Divine Flame SSCoach Points: 15943 More actions · Answer 8

You can use the Server Side Traces also instead of GUI, they consume less resources on server. You can read about them here[/url].

To read how to create server side traces using profiler click here[/url].

Sujeet Singh