November 22, 2004 at 2:42 pm
ladies and gentlemen,
a bit of advice please. i believe i understand how SQL Server impements schemas (aka users), the dbo user, db_owner fixed database role and so on. i do remember reading a best practices recently, but im sure this just went into creation of objects by specific users, and didn't answer the following questions:
1) what do you believe the best practice for ownership of object creation is when administering a development/staging (UAT) environment? i ideally want to control the environment so all users have the flexibility to create objects under the context of dbo, so it's not down to me to create them within the dev environment. is the best way to grant developers the db_owner fixed database role and get them to create objects in the format [dbo].[object_name], does this result in true dbo ownership of objects?
2) whats the best practice for database ownership, currently i'm in a dilema? i have inherited a production environment, where all databases have been created under the context of an NT domain administrator login thats a member of the sysadmin server role. i.e. domain\wladmin (NT domain administrator login) is listed as the database owner within all system and user databases. i am currently auditing and reviewing security and am planning on revoking sysadmin rights for the wladmin account. i will replace this with two SQL Server specific windows accounts and a windows global group (for improved traceability etc). what account would you suggest should be the new database owner of users and system databases, one of the new windows accounts, the windows global group, sa (im certain that's not a sensible choice), another account? and can you forsee any issues with permissions if i revoke sysadmin rights to the wladmin account before changing the ownership of the databases?
any input would be greatly appreciated
thanks,
lloyd
November 22, 2004 at 4:04 pm
For the past 5 years I have ,
(A) allowed Developers dbo_owner Role to their Databases and get them to create objects in the context of [dbo].[object_name].
(B) Made sa the owner of all user & system Databases.
With the above advise, I can confidently say that you will not experience any object ownership issues.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply