February 13, 2008 at 1:00 pm
All of the databases on the server currently have the owner set as the 'sa' account. I read that this is bad practice and that a database owner should be a specific user.
I tried to change the database owner to a user and get an error that the 'proposed database owner is mapped as a user on this database'.
Who should I set the owner of the database to? Is there a special login to use? If the owner is not a user does the owner still have access?
Thanks.
February 13, 2008 at 2:08 pm
Books OnLine will give you info about using the procedure sp_changedbowner to replace "sa" with another owner. You are correct, you cannot change an existing user with access to the database into the new owner. You must drop that user from the database and then run the above procedure to re-add them as the database owner.
The database owner has all permissions to the database.
HTH
Elliott
February 13, 2008 at 2:36 pm
Thank you! 🙂
February 14, 2008 at 12:01 am
hey, where did you read that it was a bad practice to have database owned by 'sa' ? Can I get a link?
thx
-----------------------------
www.cbtr.net
.: SQL Backup Admin Tool[/url] :.
February 14, 2008 at 4:41 pm
Yes wondering out where is it specified that you should not use sa as a database owner. The link of that will let us know what the bad practice it is.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
February 14, 2008 at 5:35 pm
I've heard the exact opposite. You should never have a user own a database unless absolutely necessary. If the user account is dropped and they still own a database it can cause issues.
-SQLBill
February 14, 2008 at 5:37 pm
I had no idea that it was a bad idea either..
hmm, I would really like to know that too.
thx
John Esraelo
Cheers,
John Esraelo
February 14, 2008 at 6:10 pm
I read it in the Microsoft technical article that you can download here:
This is the part of the article that I read:
Best practices for database ownership and trust
• Have distinct owners for databases; not all databases should be owned by sa.
• Minimize the number of owners for each database.
• Confer trust selectively.
• Leave the Cross-Database Ownership Chaining setting off unless multiple databases are deployed at a single unit.
• Migrate usage to selective trust instead of using the TRUSTWORTHY property.
I'm not sure how much it matters or why. It may not be that big of a deal. Does anyone know why its better to have distinct owners instead of having databases owned by sa?
February 14, 2008 at 6:28 pm
SQLBill (2/14/2008)
I've heard the exact opposite. You should never have a user own a database unless absolutely necessary. If the user account is dropped and they still own a database it can cause issues.
Windows logins are especially prone to this. Therefore, if you go this direction, it's probably best to create specific SQL Server logins with a specific naming convention (not just the name of the database, that's too easy for an attacker) that helps you identify that the user is a database owner.
Also, since this is in a SQL Server 2005 forum, if you've got SP2 installed, you can use a DDL trigger to prevent the dropping of such logins.
K. Brian Kelley
@kbriankelley
February 14, 2008 at 6:30 pm
ahenderson (2/14/2008)
I read it in the Microsoft technical article that you can download here:This is the part of the article that I read:
Best practices for database ownership and trust
• Have distinct owners for databases; not all databases should be owned by sa.
• Minimize the number of owners for each database.
• Confer trust selectively.
• Leave the Cross-Database Ownership Chaining setting off unless multiple databases are deployed at a single unit.
• Migrate usage to selective trust instead of using the TRUSTWORTHY property.
I'm not sure how much it matters or why. It may not be that big of a deal. Does anyone know why its better to have distinct owners instead of having databases owned by sa?
Aye, the rationale for this is in case cross database ownership chaining does get turned on. It's on (and required to be on) for master and tempdb. It's also on for msdb, but this can be turned off. If the owner of two databases is the same and cross-database ownership chaining is on for both (or on server wide), that objects belonging to any schema owned by dbo map to the same login. That means, ta da, no permissions check, access to the object is granted if referred to by an object in the other database within said schema. Hence the reason for the recommendation.
K. Brian Kelley
@kbriankelley
February 15, 2008 at 4:10 am
that article says 'not all databases should be owned by sa' which to me does not have the same meaning as 'do not have sa as owner of all databases'.
the article is also talking about practices for ISP's as opposed to totally in house SQL instances. For an ISP hosting databases for multiple companies in one instance it makes sense not to use sa but an id provided to that company which has ownership of the database. Obviously whoever manages the ISP instance doea not want to give the sa id out to the hosted companies., but he (or she) wants them to be able to manage their own databases.
If its totally in house I do not see why it is bad practice to use sa, after all sysadmins get automatic ownership rights on all databases anyway.
---------------------------------------------------------------------
February 15, 2008 at 7:08 am
George, it's an extra layer of protection should cross-database ownership chaining get turned on by mistake. Not all, makes sense. Keep in mind, the protection isn't to restrict sysadmin level accounts. It's to restrict lower accounts that could bypass a permission check due to cross-database ownership chaining. If you have the databases owned by different users, dbo doesn't map to dbo. Therefore, one setting does not break you.
K. Brian Kelley
@kbriankelley
February 15, 2008 at 7:41 am
Brian, I understand what you are saying, but along with the risk is the LIKLIEHOOD of the risk occurring and leading to the effect described. It seems to me this risk is too low to lead to the conclusion that it is a BAD practice to have sa as the owner of user databases.
I feel that the benefit of using sa in that it helps decrease the likliehood of broken ownership chains within the database outweighs the cross ownership issue
---------------------------------------------------------------------
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply