- From: "Russell Fields" <russellfields@xxxxxxxxxx>
- Date: Tue, 11 Aug 2009 13:59:40 -0400
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
GRANT REFERENCES ON DATABASE::MyDb
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.
"Zamdrist" <zamdrist@xxxxxxxxx> wrote in message news:2181785a-ad38-4b14-8825-e6ad390530b7@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
On Aug 11, 9:46 am, "Russell Fields" <russellfie...@xxxxxxxxxx> wrote:
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:
where is_member(name) = 1
If db_ddladmin is not in the list then there was some problem in applying
"Zamdrist" <zamdr...@xxxxxxxxx> wrote in message
> 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.
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.