Q: In our development environment, everyone has the system administrator (SA) password, and 5 groups use a database. After a developer accidentally deletes data, I have no way of determining which data to delete. How do you approve a workstation or login ID when a developer updates data in a database?
A: Because you work in a development environment, the performance penalty of using SQL Profiler and server tracking does not affect the production environment, so you can set up a server trace to record the information in a table in the background. You can then search the table to determine who deleted the data.
Obviously, assigning an SA account to multiple users at once can pose many problems. Here are some of the steps you should take:
Create a copy of the database for each development team and provide each team with an account that only the database owner has access to. In this way, they will not affect each other.
If each developer needs SA access (for example, running SQL Profiler tracking) and you are running SQL Server Enterprise or Developer Edition, you can provide each team with SQL One instance of the server and its dedicated sa password.
Consider setting up a development environment for each project and managing conflicts among all projects by a single version of the database administrator in an integrated environment. In this way, because the schema change scripts have been tested before they enter the integration environment, the quality of the production builds will improve.
-the Microsoft SQL Server development team