Unanswered: Is there a way to track user access to db?
I need to be able to track which users and the number of times a user connects to a particular SQL Server database. I have a simple SQL Server database that users query (SELECT only) using an Access 2000 front-end. I do not have any control over the front-end so my only tools on the database side. I can't use 3rd party software, just SQL Server. Is there any way? Thanks.
You can track logon successes and failures within SQL Server. Using SQL Enterprise Manager, Admins can simply select the appropriate Audit Level option on the Security tab of the Properties dialog box for a SQL Server DB instance.
Open EM, right click on the DB Instance name from the left hand tree. Select Properties. Click on the Security tab, then select the Successes radio button to log all the successful connections.
A message will be written to the Windows NT/2000 event log, the SQL Server error log or both indicating the time, date and user who logged in. Go to the Windows Event viewer and check the Security log, you will see an event for each db login.