What you can do is restrict access to the SQL Server and the Windows server that SQL is on. There are server level roles
, with sysadmin
giving you the highest privileges. Review Server-Level Roles to see what you will need to give your temp. The temp should never be in the sysadmin
server level role, based on what I read from your requirements.
Then there are database level roles
. This is set at each database level for each DB. See Database-Level Roles to read more. Your temp may be in the db_backupoperator
, or the db_datareader
role for each DB. This will prevent him from deleting/modifying the DBs.
Encrypt your confidential data. You can also use passwords for your backups, but this feature is not meant for security, rather is helps maintain related backup sets.
On the Windows server part, you can further restrict access. Querying the SQL Server can be done from a laptop with SSMS installed on it, thereby not having to give your temp much rights on the Windows server.
To track changes on the DB you will need auditing. There is a plethora of information in the Auditing in SQL Server 2008 Microsoft technical article.
Remember, too much auditing comes at the cost of performance. With SQL Server 2008, there are new and much granular auditing controls, like CHANGETABLE
and policy-based management. These can help with your Tracked down, Proven, and Rectified requirements.
Above is just a quick summary of the possibilities.
To track object use with the SQL Server Audit feature, it’s necessary to set up the auditing. In order to do so, an audit object must be created first.
This can be done using SQL Server Management Studio or T-SQL.
The next step is to set up the auditing in the particular database on specific objects. This is where availability of the SQL Server Audit feature is required on the database level (the database level auditing is available in SQL Server Enterprise and Developer editions only).
To continue setting up the auditing, it’s required to create a database level audit specification. Such database level audit specification will belong to the audit object previously created.
Although SQL Server provides a built-in feature (the View Audit Logs context menu option of an audit object) to view captured information, this is not a convenient way for creating comprehensive reports, and it provides basic filtering only.
So, in order to provide tracked information for any deeper analysis or documenting, use the fn_get_file_audit SQL Server function to read repository .sqlaudit files used by the audit object.
Best Answer
One consideration would be to query the sys.dm_audit_actions view at regular intervals to see what new information has been logged to the audit log. For example, create a stored procedure to mail the results of a query from this view to a set of people on a daily basis. The only drawback would be that it wouldn't be delivered as it happens (asynchronously) unless you wanted to script it in .Net.