How to Manage Multiple Developers Using the System Administrator Account

[Sunite]

How to Manage Multiple Developers Using the System Administrator Account

Q
In our development environment, everyone has the system administrator (sa) password, and five groups use one database. Occasionally, developers accidentally delete data and I have no way of determining who deleted which piece of data. How can I audit the workstation or login ID when a developer updates data in the database?



A


Because you're working in a development environment where SQL Profiler and server tracing are suitable for use (that is, 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 database owner access only. This way, they can't interfere with one another.

 

If each developer needs sa access (to run SQL Profiler traces, for example) and you're running SQL Server 2000 Enterprise or Developer Edition, give each team an instance of SQL Server with its own sa password.

 

Consider setting up one development environment per project as well as an integration environment controlled by a release database administrator 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.