none
Need to limit access on sandbox

    Question

  • I need a way to give developers access to my SQL Server sandbox without giving them db_owner privileges.  Ideally, I would like to give them the ability to create objects within the database and do so under the dbo schema so I don't run into ownership chain issues.  I thought of giving them ddl_admin privileges but need to know what else to grant them in order to make sure all objects they create or alter are owned by DBO.  Please help!

    Lyn

    Monday, October 14, 2013 5:23 PM

Answers

All replies

  • If you want to limit devs to the dbo schema, grant control on only that schema.  For example:

    GRANT CONTROL ON SCHEMA::dbo TO Dev_Role;
    


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Monday, October 14, 2013 5:27 PM
    Moderator
  • Ideally, I would like to give them the ability to create objects within the database and do so under the dbo schema so I don't run into ownership chain issues.  ..

    Besides the correct answer from Dan:

    What makes dbo "ideal" for such a demand?

    That's roughly comparable of putting all UserFiles in one Folder C:\Users without subfolders. If you are afraid of broken ownership-chains, there are multiple options. The easiest is to have the new schema(s) (all) owned by “dbo”. Then it just behaves the same as the dbo-schema. No need to throw everything there.



    Andreas Wolter | Microsoft Certified Master SQL Server

    Blog: www.insidesql.org/blogs/andreaswolter
    Web: www.andreas-wolter.com | www.SarpedonQualityLab.com

    Monday, October 14, 2013 6:16 PM
  • what would be the command to have all schemas owned by DBO? 

    Monday, October 14, 2013 8:13 PM
  • Thanks Dan.  Works great!  Does that take away the user's ability to ever create objects under his own schema?  I tried to explicitly create a table as user1.mytable but that did not work.  Can a user have control or the DBO schema and his own?  If so, please show me how.

     

    Monday, October 14, 2013 8:16 PM
  • what would be the command to have all schemas owned by DBO? 

    There is no such command - you have to change them one by one.

    To change the owner of a schema you would use:

    ALTER AUTHORIZATION ON SCHEMA::SchemaName To dbo

    be aware though, that after changing schema ownership, all explicit permissions onto that schemas are lost. (also documented)

    http://technet.microsoft.com/en-us/library/ms187359.aspx

    By the way: All Schemas are owned by the User that created them (which happens to be "dbo" quite often)


    Andreas Wolter | Microsoft Certified Master SQL Server

    Blog: www.insidesql.org/blogs/andreaswolter
    Web: www.andreas-wolter.com | www.SarpedonQualityLab.com

    Monday, October 14, 2013 8:20 PM
  • Thanks Andreas.  Does this prevent the user from ever being able to explicitly create objects under his own schema? 

    For example, create table user1.mytable?  I tried and it failed.

    Monday, October 14, 2013 8:34 PM
  • Does this prevent the user from ever being able to explicitly create objects under his own schema? 

    For example, create table user1.mytable?  I tried and it failed.

    If the schema is owned by the user, they will have CONTROL permissions:

    CREATE SCHEMA user1 AUTHORIZATION user1;
    GO


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Monday, October 14, 2013 8:36 PM
    Moderator
  • Thanks Andreas.  Does this prevent the user from ever being able to explicitly create objects under his own schema? 

    For example, create table user1.mytable?  I tried and it failed.

    There is no such thing as a "own" schema - UNLESS you are on a SQL Server before 2012 and using Windows-Groups as a Login-->User.

    A user only has permissions onto a schema that 1) exists 2) he has the permission on

    simple :)


    Andreas Wolter | Microsoft Certified Master SQL Server

    Blog: www.insidesql.org/blogs/andreaswolter
    Web: www.andreas-wolter.com | www.SarpedonQualityLab.com

    Monday, October 14, 2013 8:37 PM