Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Database Owners Expand / Collapse
Author
Message
Posted Tuesday, March 05, 2013 10:03 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 9:27 AM
Points: 504, Visits: 1,463
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?



Post #1426913
Posted Tuesday, March 05, 2013 10:37 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 3:33 PM
Points: 3,731, Visits: 7,069
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; They'll drag you down to their level and beat you with experience"
Post #1426935
Posted Tuesday, March 05, 2013 10:30 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:25 AM
Points: 7,070, Visits: 12,523
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

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy
Post #1427169
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse