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

db_ddladmin role without 'drop' capability

Hello -
I'm looking for a way to assign a user db_ddladmin
permissions on a particular database, but without the DROP
functionality. I want them to be able to do everything
that the role entails, but not to be able to drop tables
etc. Any help would be greatly appreciated...thanks!
MatthewHi,
If you provide db_ddladmin role you cant restrict the user from drop
command. Because the DENY or REVOKE
command can not be granted.
So alternative is provide the roles 'db_datareader', 'db_datawriter' the
user and provide explicit grant to
create table,create proc,create function,create view.
Sample:-
sp_addrolemember 'db_datareader','user'
go
sp_addrolemember 'db_datawriter','user'
go
grant create table,create proc,create function,create view to <user>
Thanks
Hari
MCDBA
"Matthew" <anonymous@.discussions.microsoft.com> wrote in message
news:017601c46dd3$52520310$a301280a@.phx.gbl...
> Hello -
> I'm looking for a way to assign a user db_ddladmin
> permissions on a particular database, but without the DROP
> functionality. I want them to be able to do everything
> that the role entails, but not to be able to drop tables
> etc. Any help would be greatly appreciated...thanks!
> Matthew|||Thanks Hari, much appreciated!

>--Original Message--
>Hi,
>If you provide db_ddladmin role you cant restrict the
user from drop
>command. Because the DENY or REVOKE
>command can not be granted.
>So alternative is provide the
roles 'db_datareader', 'db_datawriter' the
>user and provide explicit grant to
>create table,create proc,create function,create view.
>Sample:-
>sp_addrolemember 'db_datareader','user'
>go
>sp_addrolemember 'db_datawriter','user'
>go
>grant create table,create proc,create function,create
view to <user>
>--
>Thanks
>Hari
>MCDBA
>"Matthew" <anonymous@.discussions.microsoft.com> wrote in
message
>news:017601c46dd3$52520310$a301280a@.phx.gbl...
DROP[vbcol=seagreen]
>
>.
>

DB_DDLAdmin Role in SQL

Hello:

I have read that giving a User the DB_DDLAdmin role in SQL might causes problems with ownership chains in the future. Since the User will have ownership to all objects created, what preventive measures can one take to help avoid any problems which might loom in the distant future due to ownership chains?

Thank you,

-H

Hi WebD,

A user with db_ddladmin role just means that the user is authorized to run any DDL (data defination language) command in the database. So, based on my understanding, I don't think it will cause server security troubles in the distance futer. As to database security configurations, I think the best practices are to avoid assigning server roles to users but instead, assigning database levels to users to make sure users are only authorized to some particular databases and not the whole databases installed in your instance.

You can refer to this article for more detailed information and better explanation:http://www.sql-server-performance.com/articles/dba/sql_security_p1.aspx

Hope my suggestion helps

This response contains a reference to a third party World Wide Web site. Microsoft is providing this information as a convenience to you. Microsoft does not control these sites and has not tested any software or information found on these sites; therefore, Microsoft cannot make any representations regarding the quality, safety, or suitability of any software or information found there. There are inherent dangers in the use of any software found on the Internet, and Microsoft cautions you to make sure that you completely understand the risk before retrieving any software from the Internet.

sql