June 4, 2004 at 11:53 am
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
June 4, 2004 at 3:11 pm
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.
June 4, 2004 at 3:22 pm
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...
June 5, 2004 at 9:46 am
sp_changeobjectowner will also allow you to change the owner to dbo.
June 7, 2004 at 8:34 am
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
June 7, 2004 at 11:49 am
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.
June 7, 2004 at 12:01 pm
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