Creating Objects in EM: dbo vs developer owned schemas??

  • Hello,

    * MSSQL 2000 SP3; Enterprise Manager (EM)

    In our development environment, app developers are added to the database roles: db_ddladmin, db_datareader and db_datawriter.

    When they create a table, via EM, they get several warning boxes "You are not logged on as the owner or system administrator. You might not be able to save changes to tables to tables that you do not own". Selecting 'OK' allows them to continue to create a table. The table when saved, is automatically pre-fixed with their own account name ie: DOMAIN\DeveloperName.Emp. They then use DTS to copy this table and data, and re-import it all back to the same location but change the schema to dbo ie: dbo.Emp under "Destination".

    Is there a way to avoid this extra step of using DTS to allow a developer to create the object with the prefix "dbo"? We would prefer that all objects be owned by "dbo" (re: best practices).

    Many thanks. Jeff

  • In the Table Creation Window there is a little Script Button. It looks like a scroll. Create the fields and settings but don't save. Script the object. Close the creation screen. Change the owner of the table in the script to dbo then run in query analyzer.

    Its simpler than dts I would think. Hope it helps.

  • I wasn't too clear. When it scripts it will have

    Create Table owner.tablename

    Just change that line to

    Create Table dbo.tablename

    Then run in query...

    You can also run

    sp_changeobjectowner @objname = 'tablename' , @newowner = 'dbo'

    ddl admin rights users should be able to do that...

  • sp_changeobjectowner will also allow you to change the owner to dbo.

  • Hi,

    Thanks for all the inputs. A little FYI ...

    From testing, I discovered that using sp_changeobjectowner in QA DOES NOT work with db_ddladmin. It requires db_ddladmin AND db_securityadmin permissions. BOL also has a little blurb on this ...

    "sp_changeobjectowner  ... Permissions - Only members of sysadmin fixed server role, the db_owner fixed database role, or a member of both the db_ddladmin and db_securityadmin fixed database roles can execute sp_changeobjectowner."

    Many thanks. Jeff

  • Jeff,

    we have the same security model for our developers but we also grant them db-securityadmin. FYI. With secuity admin, users can only create roles and grant permissions to user defined roles. they cannot grant access to system defined roles i.e. db_datareader etc.

  • sa24,

    Many thanks for the tip. I'm testing it now and it looks like it will fit our dev/prod deployment model as well.

    Many thanks. Jeff

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply