SQL Server really doesn't provide this functionality naturally. There are a number of products such as SQLAudit (http://www.redmatrix.com/default.asp?section=sqlaudit).

While SQL Server does have it's transaction log, it's not really designed for this type of work. It's really there to enforce transaction ACID properties. But, there are products that will allow you to view the transaction log (http://www.lumigent.com/LogExplorer/developer.htm).

-Mike