none
SQL Server db_ddladmin

    Question

  • Hi All,

     I have a user which is in the "db_datareader", "db_datawriter" and "db_ddladmin" DB roles, however when he tries to modify a table with SSMS he receives a message saying:

    You are not logged in as the database owner or system administrator. You might not be able to save changes to tables that you do not own.

    Of course, I would like to avoid such message, but until now I did find the way.Could you please advise on this.

    Thursday, September 19, 2013 12:49 AM

Answers

  • Hi BomaSri,

    According to your description, I understand that you want to allow certain users to create/modify objects on a database and don't want to give the db_owner permission to certain users. While when you use db_ddladmin role to make some operation, it doesn't work as expected.

    The members of the db_ddladmin fixed database role can run any Data Definition Language (DDL) command in a database. For the detailed information on the db_ddladmin permission, please refer to the link: http://technet.microsoft.com/en-us/library/ms189612(v=sql.105).aspx

    We can use db_ddladmin fixed database role to create/modify objects on a database, however, there are limitations to the modifications we can make. As we modify an existing table or design a new one, our work can induce attendant modifications in other tables. For example, if we change the data type of a foreign-key column, the corresponding column in the primary-key table will be automatically modified by the Visual Database Tools. While we do not own the primary-key table, and we are not logged in as the system administrator, database owner, or a user that is a member of the db_owner role, our modification will fail.

    In this case, we should pay attention to the scope of the db_ddladmin fixed database role, then make some corresponding modification.

    Regards,
    Heidi Duan

    Wednesday, September 25, 2013 6:10 AM

All replies

  • Hi All,

    I want to allow certain user to create/modify objects on a database(tables, views, sp's, functions, schemas etc) and don't want to give db_owner.

    I thought I could do it with the db_ddladmin role, but somehow that doesn't wort as expected. The error messages i get is that are like this:

    Your are not loggend on as the database owner or system administrator. 
    You might not be able to sache changes to tables that you do not own.
    

    Thursday, September 19, 2013 12:57 AM
  • Hello,

    Does the user only get this "info message" or is he really not able to do modifications on the tables?


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Thursday, September 19, 2013 4:12 AM
  • Hi,

    User  can do modifications but with the mensioned "error messages".But I dont want to give user db_owner permissions.Any solution for this

    Thursday, September 19, 2013 1:29 PM
  • Is the user trying to write to sys tables?  The error message you mentioned may or may not stop the user from executing a transaction.  

    Thursday, September 19, 2013 6:41 PM
  • Hi BomaSri,

    According to your description, I understand that you want to allow certain users to create/modify objects on a database and don't want to give the db_owner permission to certain users. While when you use db_ddladmin role to make some operation, it doesn't work as expected.

    The members of the db_ddladmin fixed database role can run any Data Definition Language (DDL) command in a database. For the detailed information on the db_ddladmin permission, please refer to the link: http://technet.microsoft.com/en-us/library/ms189612(v=sql.105).aspx

    We can use db_ddladmin fixed database role to create/modify objects on a database, however, there are limitations to the modifications we can make. As we modify an existing table or design a new one, our work can induce attendant modifications in other tables. For example, if we change the data type of a foreign-key column, the corresponding column in the primary-key table will be automatically modified by the Visual Database Tools. While we do not own the primary-key table, and we are not logged in as the system administrator, database owner, or a user that is a member of the db_owner role, our modification will fail.

    In this case, we should pay attention to the scope of the db_ddladmin fixed database role, then make some corresponding modification.

    Regards,
    Heidi Duan

    Wednesday, September 25, 2013 6:10 AM