March 5, 2013 at 10:03 am
It recently came to my attention that the DB owner on many of our databases is the user who created them, rather thas sa or a service account.
Assuming it is OK for that person to have full rights on the database, how much of a problem is this? Does anything break if the person leaves the organization and the account is disabled? (I believe Agent jobs owned by diabled Active Directory accounts DO stop working).
What are the pros & cons of having databases owned by sa vrs domain service accounts?
March 5, 2013 at 10:37 am
If a DOMAIN account is the db owner and that account is deleted/disabled (for whatever reason), and Agent Job tied to it will fail, and any database owned by it will still work, but you won't be able to make any changes to it until the user is removed and a "proper" owner is granted as the owner.
If you use a proxy/service account to run things on your server and "that" account is tied to the account then no process tied to it will run (be careful of this one)
IMHO, I would create a routine that updates the db owner to either an internal SQL account (i.e. sa) or to the service account (one that has been set up in active directory to never get locked out))
Pros & cons of having databases owned by sa vrs domain service accounts?
SA
Pro - most likely account will never be disabled
Con - prolly shouldn't tie this account to anything (many people disable it altogether)
Domain Service Account
Pro - external to SQL
Pro - limited access to anything else (if set up properly) but has access within SQL
Con - some nitwit/new hire in IT could say "what's this account for?" drop the account...
😀
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
March 5, 2013 at 10:30 pm
Two separate questions:
1) Who should own databases?
It does not matter if the Server Login that owns a database is disabled or enabled. sa is a good default choice to own all databases, regardless of whether the Login is enabled as the Login's status will not affect database operations.
What does matter is whether the Server Login that owns a database has been deleted from the instance, or if owned a domain account that entered the instance via an Active Directory Group whether the account has been deleted from Active Directory. In the case when the Login or underlying account is deleted, if you're leveraging cross-database ownership chaining you might start to see problems. The worst issue the average installation might encounter if a Login or Account that owns a database is removed is you might see an error when right-clicking on the database in SSMS Object Explorer and selecting Properties.
2) Who should own Agent Jobs?
Same story here for the most part. sa is also a good default choice to own all Jobs even if the Login is disabled. When sa owns a job, or any Login in the sysadmin Role for that matter, the job will execute under the context of the SQL Server Agent Service account.
--
One of the things I want to know when I takeover a new instance, and also check periodically through either Policy Based Management or a simple job that emails me a report, is which databases and jobs exist in the environment that are not owned by sa, especially the ones owned by Server Logins or Active Directory accounts that no longer exist. I then explore whether I can standardize those databases and jobs to be owned by sa.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply