Re: db_ddladmin



For my implementation, it is simple. Actually, the Windows Group through which I grant these rights does not even have a specific default schema nor does it own any schemas. My tables are all owned by dbo.

The only other permission that my db_ddladmin group has is:
GRANT VIEW DEFINITION ON DATABASE::MyDb
TO [Domain\DDLAdminGroup]
GRANT REFERENCES ON DATABASE::MyDb
TO [Domain\DDLAdminGroup]

A few more questions about the database user in question:
Does it have a default schema?
Is it the owner of a schema?
Are they using fully qualified object names (e.g. dbo.TableName)?
What error message do they get when their attempt to alter an object fails?

Also, if you have a DDL Trigger on the database, make sure it is not interfering with their work.

RLF

"Zamdrist" <zamdrist@xxxxxxxxx> wrote in message news:2181785a-ad38-4b14-8825-e6ad390530b7@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
On Aug 11, 9:46 am, "Russell Fields" <russellfie...@xxxxxxxxxx> wrote:
Zamdrist,

I have one database where developers are only db_dlladmin and they can
create, alter, etc. objects throughout the database. I don't know why you
are having problems with that.

Have the problem user(s) run the following query:

select name
from sys.database_principals
where is_member(name) = 1

If db_ddladmin is not in the list then there was some problem in applying
that permission.

RLF

"Zamdrist" <zamdr...@xxxxxxxxx> wrote in message

news:5e55b197-2658-43b4-8e16-c9328108637a@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

> Yeah I'm just trying to fit the user into a role where they can
> create, drop, modify tables in a database without having full database
> rights, such as dropping the database.

> SQL 2005

> I didn't think I would need to add permission to alter tables on an
> individual table by table. I thought that is what roles were for, i.e.
> ddladmin.

> Thanks

db_ddladmin is listed. I suspect because the table is owned by dbo,
even though they are part of the db_ddlamin role, they cannot modify
the table's structure. I presume they must also be owner of the table.

So I tried to change the ownership of the table from dbo to the user,
but it then complains to me that the user cannot be found.

"Database principal or schema 'cmintake_rw' does not exist in this
database.". Its right there, I can see it with my own two eyes. Can
even log into the database as the user.

What gives? I thought this was pretty straightforward.

.