SQL Server 2005以上可以通过创建审计来查看用户的所有操作,包括时间,操作内容,字段信息等,举个例子,select审计USE master CREATE SERVER AUDIT testAudit TO FILE(FILEPATH='D:\TempSelectAudit\') GO CREATE DATABASE AUDIT SPECIFICATION testAudSpec FOR SERVER AUDIT testAudit ADD (SELECT ON HumanResources.Employee BY Public) GO USE master SELECT is_state_enabled,* FROM sys.server_file_audits USE AdventureWorks SELECT is_state_enabled,* FROM sys.database_audit_specifications GO USE master ALTER SERVER AUDIT testAudit WITH (STATE=ON) GO USE AdventureWorks ALTER DATABASE AUDIT SPECIFICATION testAudSpec WITH (STATE=ON) GO SELECT session_server_principal_name, statement, * FROM fn_get_audit_file ('D:\TempSelectAudit\*',NULL, NULL)
另外就是修改前台程序,发送tsql指令的时候做记录。
CREATE SERVER AUDIT testAudit TO FILE(FILEPATH='D:\TempSelectAudit\')
GO
CREATE DATABASE AUDIT SPECIFICATION testAudSpec FOR SERVER AUDIT testAudit
ADD (SELECT ON HumanResources.Employee BY Public)
GO
USE master
SELECT is_state_enabled,* FROM sys.server_file_audits
USE AdventureWorks
SELECT is_state_enabled,* FROM sys.database_audit_specifications
GO
USE master
ALTER SERVER AUDIT testAudit WITH (STATE=ON)
GO
USE AdventureWorks
ALTER DATABASE AUDIT SPECIFICATION testAudSpec WITH (STATE=ON)
GO
SELECT session_server_principal_name, statement, *
FROM fn_get_audit_file ('D:\TempSelectAudit\*',NULL, NULL)