- dbo rights are given to developpers so that they can create objects in DBo schema;
- sa rights are given to developpers so that they can use profiler to to debug a stored procedure.
The first issue comes as a by product of how schema defined in SQL Server 2000; It follows a simple formula of schema = user. To avoid users from creating tables in their user schema (and move to another schema when they leave the company) DBAs encouraged all to use DBO schema. That gives full access to developpers even to drop existing objects.
SQL 2005 handles it very well by seperating users from schema. User may use a default schema. However, schema is simply a logical collection of objects.
Second scenario is even serious. To run profiler, a user should be able to have sa rights. A person with sa right can do anything. including removing all other users (this includes the actual "DBA" as well) BANG! Currently I was avoiding this by running a profiler against the "profile user" to identify whats going on!
SQLS erver has solved this issue by allowing explicit access to run profiler.
Let me explore and share more security features later.