In our development environment, everyone has the systems administrator (sa) password, and five groups use one database. Occasionally, developers accidentally delete data, and I have no way of determining who did what. How can I audit the workstation or login ID when a developer updates data in the database?
Because you're working in a development environment where SQL Server Profiler and server tracing are suitable for use (i.e., the potential performance impact doesn't affect production), you could set up a server trace to log information to a table in the background. Then, you could search this table to see who was deleting data. Obviously, assigning multiple users to the sa account at one time is problematic. Here are some courses of action you could follow:
- Create a copy of the database for each development team, and give each team an account with dbowner access only. This way, they can't interfere with one other.
- If each developer needs sa access (to run Profiler traces, for example) and you're running the SQL Server 2000 Enterprise or Developer Edition, give each team an instance of SQL Server with its own sa password.
- Many professional development shops set up a development environment per project, as well as an integration environment controlled by a release DBA who manages any interproject clashes. When you organize the shop this way, the quality of production releases increases because the schema-change scripts have already been tested by release into the integration environment.