SQL Server includes a variety of tools to help you log and audit activity:
This creates logon auditing. After the common criteria compliance enabled option is enabled, login auditing is enabled. Each time a user successfully logs in to SQL Server, information about the last successful login time, the last unsuccessful login time, and the number of attempts between the last successful and current login times is made available. These login statistics can be viewed by querying the sys.dm_exec_sessions dynamic management view. See http://msdn.microsoft.com/en-us/library/bb326650.aspx for more information.
sp_configure 'show advanced options', 1; GO RECONFIGURE; GO sp_configure 'common criteria compliance enabled', 1; GO RECONFIGURE GO
C2 Audit mode (older method)
C2 audit mode can be configured through SQL Server Management Studio or with the c2 audit mode option in sp_configure. Selecting this option will configure the server to record both failed and successful attempts to access statements and objects. This information can help you profile system activity and track possible security policy violations.
C2 Audit Mode data is saved in a file in the \MSSQL\Data directory of default instances, and the \MSSQL$instancename\Data directory of named instances. If the audit log file reaches its size limit of 200 megabytes (MB), SQL Server will create a new file, close the old file, and write all new audit records to the new file. This process will continue until the audit data directory fills up or auditing is turned off.
You can use database triggers to log activity into a custom table
With SQL Server 2008 Enterprise, you can use the new SQL Server Audit functionality
From within Management Studio you can use the new Audits option. The new SQL Server Audit feature introduced in SQL Server 2008 represents a significant improvement over the auditing capabilities offered in previous versions of SQL Server. One of the key advancements is the introduction of fine-grained auditing whereby events can be targeted to specific actions on an object by particular principals; the objects can even be scoped down to the individual table level. Performance is another big incentive, because SQL Server Audit performs significantly better than a comparable SQL Trace, up to around 45% in some cases. Additionally, the audit target is no longer confined to just files, because the Windows Application and Security logs are now options. And considering benefits such as persistence of the audit state between instance restarts and the involuntary recording of changes to the audit state, the new SQL Server Audit feature provides a robust and comprehensive auditing solution for the enterprise.
See Microsoft's article here: http://msdn.microsoft.com/en-us/library/dd392015(v=sql.100).aspx