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.

.

Relevant Pages

  • Re: Complete Neophyte Question(s)
    ... No you cannot remove a login from the 'public' role. ... For rights to _use_ objects then the appropriate rights need to be granted ... GRANT SELECT ON dbo.Orders TO OrderViewers ... for a database that is supposed to be secured it is a bad idea ...
    (microsoft.public.sqlserver.security)
  • Re: SQL Server 2005 Stored Procedure security annoyances
    ... stored proecedure on a database wide level? ... GRANT EXECUTE ON SCHEMA::MySchema TO MyRole ... I am sure I will forget some SP's and probably forget to set the rights ... permissions because not all stored procedures are equal. ...
    (microsoft.public.sqlserver.security)
  • Re: Java is becoming the new Cobol
    ... In the Unisys 2200 environment using their Network Database Server, you get a schema work area depending on how you invoke the schema. ... However, it not only copies the schema area into the program, but the "DMCA" (Database Management Communication Area - basically, the block of information that holds your connection state) is copied in too. ... So, you're left with working-storage, which makes it visible to your program only, or common-storage, which makes it visible to programs linked in. ...
    (comp.lang.cobol)
  • Re: Renaming databse objects
    ... we may need to change the database name and in some cases want to rename ... existing database objects. ... Just in case this database object renaming project is in response to ... Create a new schema, ...
    (microsoft.public.sqlserver.programming)
  • Split messages
    ... This is based off the northwind database. ... Create document schema: ... Select the SQL entry, make sure that we are pointing to the SQL ... For the item select a receive pipeline and name it EmpSplitPipe.btp. ...
    (microsoft.public.biztalk.general)