db_owner VS db_ddladmin roles

We have several departamental "database administrators" that needs access to their databases "only" and cannot perform maintenance tasks administrative tasks such as backup and create new server login. We basically function as a "database hosting services" to these departamental dbsa. I granted rights to these departamental dbas to their database and I assigned the db_ddladmin role to them. They can create the objects within their database but they cannot read the records because when the table was created it belongs to the dbo schema - I don't want to assign them to the db_owner role, this role is much more permission that they need.

My question is: What is the best way to give these departamental dbas rights to manage their databases without having too much permission to maintain the database permission and settings?

You need to provide more information.

Please list the actions you wish to allow, and the actions you wish to prohibit

Then we may be able to help you determine the proper mix of roles.

|||

If you are using SQL 2005 then you can take help of EXECUTE AS and audit the events to ensure they are not misusing the privilege.

All operations during a session are subject to permission checks against that user. When an EXECUTE AS statement is run, the execution context of the session is switched to the specified login or user name. After the context switch, permissions are checked against the login and user security tokens for that account instead of the person calling the EXECUTE AS statement and also check BOL for SQL 2005 for more information.

|||

The Departmental DBAs should be able to:

-Create, select, modify any objects in the database they have rights to.

-Give permission to users (such as developers that work under them) to access some objects that the departmental dbas own.

Basically they should be able to do anything needed in the database they own.

The Departmental DBAs should NOT be able to:

Create, shrink, backup databases

Basically they should not be able to change any database structure, size or settings.

BTW Do you know if there is a way for them to "see" only their database under Mngmt Studio?

thanks again

|||

In order to accomplish your goal, you would benenfit from a good understanding of how SQL 2005 uses Schemas. I suggest that you start by referring to Books Online, Topic: User-Schema Separation.

I think that by properly creating a schema, and granting your departmental dbas ownership of that schema, and then having ALL objects belong to that schema, you will be able to set this up as you want.

Using the built-in database roles, including db_owner, does NOT accomplish your goal, since the db_owner can see other databases, and even delete their database.

sql