Capturing operations and tSQL executed under a user account

All,
I am looking for a way to capture all of the SQL code executed against a
database by a specific user.
Currently, a SQL 2005 database is being maintained by a third party that
connects remotely and does occasional "maintenance." The client would like
to bring this operation in house and is looking for a way to capture these
activities so that they can be documented.
The accuracy of this is essential so we are looking for a way to do a direct
capture.
Suggestions?
Ryan Hanisco
MCSE, MCTS: SQL 2005, Project+
Chicago, IL
Remember: Marking helpful answers helps everyone find the info they need
quickly.
On Jun 7, 4:06 pm, Ryan Hanisco
<RyanHani...@.discussions.microsoft.com> wrote:
> All,
> I am looking for a way to capture all of the SQL code executed against a
> database by a specific user.
> Currently, a SQL 2005 database is being maintained by a third party that
> connects remotely and does occasional "maintenance." The client would like
> to bring this operation in house and is looking for a way to capture these
> activities so that they can be documented.
> The accuracy of this is essential so we are looking for a way to do a direct
> capture.
> Suggestions?
The easy way is to use Profiler. (That's what they called it in SQL
2000, it's undoubtedly there in SQL 2005 but possibly under a new
name.)
If you have to do it the hard way, look at the sp_trace stored
procedure.
|||Ryan Hanisco (RyanHanisco@.discussions.microsoft.com) writes:
> I am looking for a way to capture all of the SQL code executed against a
> database by a specific user.
> Currently, a SQL 2005 database is being maintained by a third party that
> connects remotely and does occasional "maintenance." The client would
> like to bring this operation in house and is looking for a way to
> capture these activities so that they can be documented.
> The accuracy of this is essential so we are looking for a way to do a
> direct capture.
This is precisely what SQL Profiler is good for. Or a server side trace,
if you want to reduce the load.
Although, this appears to me a somewhat funny thing to document procedures.
It sounds more like eavesdropping to me.n
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx