November 5, 2007 at 6:08 am
Hi, I'm looking for a "best practice" for owners of databases in SQL 2005. Should this be a named user, or a service account or some other user? What privileges should this account have if this is the case?
Tor Martin
November 8, 2007 at 2:51 am
Anyone? Is this an issue or is this unimportant?
Tor
November 8, 2007 at 4:34 am
Hi,
It is not an issue, but its a best practice to keep the db owner to the respective users, because if a user is dbo then he can do all the things in the database.
Eg.
Keep "sa" or "service account" as db owner for System dbs
For user db's keep the application FID as dbowner
Regards..Vidhya Sagar
SQL-Articles
November 8, 2007 at 11:44 pm
Every single production database of our's is set with 'sa' as the database owner. Certainly at all cost avoid making owners a user account ( even more so a Window user account ). We add application connection accounts (those that are designated for an application connection string ) as the database role of db_owner ( all rights on the database )
Last thing you need is an owner of a database leaving the company, and having their login as the owner.
Todd Carrier
MCITP - Database Administrator (SQL 2008)
MCSE: Data Platform (SQL 2012)
November 9, 2007 at 3:05 am
Keep sa as dbo because never know what issues will arise from it not being set, especially if login assigned becomes obsolete. Also I've come across some functions such as service broker which do not behave as expected when sa is not dbo.
EXEC sp_changedbowner 'sa'
November 10, 2007 at 10:26 pm
For SQL Server 2005, this is the new syntax (though sp_changedbowner still works just fine):
ALTER AUTHORIZATION ON DATABASE::*Database Name* TO *Login*
With that said, one of the cases where this matters is if you're using cross-database ownership chaining. Then any objects which are owned by dbo map to the login that owns the database. Obviously, if the same login owns a second database and cross-database ownership chaining is enabled on both (or server wide), then there isn't a permission check crossing from one database object to another.
Also, it has an impact on restores. In the case of a corrupted or missing database, the owner of the database has the ability to restore (as well as syadmin fixed server role members), but others that might have had the rights specified with the database cannot because the database cannot be read.
K. Brian Kelley
@kbriankelley
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply