Database ownership best practice

Does it matter much who owns a database? I'm reviewing security on our
SQL installation and find a number of different owners: sa, Domain
administrators and some others. I want to tidy this up. Is it best to
make an NT administrator the owner of all databases, assign a special
user which does nothing else but own databases?
Having looked at Books Online and elsewhere, I don't see any advice as
broad based as what I'm seeking.
Any suggestions would be welcome.I think it's mostly an audit trail, to see who created the database.|||Hi
Our corporate standard is that all objects must be owned by SA. If it owned
by a domain user, or a SQL Server login, if that user leaves you have to
change the ownership of the object before you can remove the user from the
DB/system.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
<stevester@.freeuk.com> wrote in message
news:1123238913.304851.90500@.g44g2000cwa.googlegroups.com...
> Does it matter much who owns a database? I'm reviewing security on our
> SQL installation and find a number of different owners: sa, Domain
> administrators and some others. I want to tidy this up. Is it best to
> make an NT administrator the owner of all databases, assign a special
> user which does nothing else but own databases?
> Having looked at Books Online and elsewhere, I don't see any advice as
> broad based as what I'm seeking.
> Any suggestions would be welcome.
>|||Like Mike Epprecht said, it's a good practice to use a standard SQL login as
the database owner since this will ensure the database owner account is
never orphaned. An orphaned database owner account can have side affects,
such as problems using sp_helpdb or using Enterprise Manager. If only
sysadmin role members can create database objects on your SQL Server, 'sa'
database ownership is probably ok. However, I'd like to add some additional
notes on the importance of the database owner.
The login associated with the database owner determines the mapping of the
database's 'dbo' user. 'dbo' has full permissions within the database so
the database owner login account needs to be appropriately secured.
Furthermore, when cross-database chaining is enabled (configurable option in
SQL 2000 SP3+ and always on in older SPs/versions), dbo-owned views, procs,
functions and triggers can access dbo-owned objects in other databases owned
by the same login even without user permissions on the referenced objects.
This is a major security concern with sa-owned databases because master is
also owned by 'sa' and is why one should enable cross-database chaining in
an sa-owned database when only sysadmin role members can create dbo-owned
objects. It is best to avoid turning on cross-database chaining unless
absolutely necessary.
If non-sysadmin role members can create dbo-owned database objects and you
must enable cross-database chaining, you should instead consider creating
and using a specialized SQL login for database ownership. See the Books
Online for more information on cross-database chaining.
Hope this helps.
Dan Guzman
SQL Server MVP
<stevester@.freeuk.com> wrote in message
news:1123238913.304851.90500@.g44g2000cwa.googlegroups.com...
> Does it matter much who owns a database? I'm reviewing security on our
> SQL installation and find a number of different owners: sa, Domain
> administrators and some others. I want to tidy this up. Is it best to
> make an NT administrator the owner of all databases, assign a special
> user which does nothing else but own databases?
> Having looked at Books Online and elsewhere, I don't see any advice as
> broad based as what I'm seeking.
> Any suggestions would be welcome.
>|||The owner info is of limited use from an audit perspective because the
database owner can be subsequently changed. See my response on security
considerations.
Hope this helps.
Dan Guzman
SQL Server MVP
<MICHAEL_SUNLIN@.COUNTRYWIDE.COM> wrote in message
news:1123249058.756243.222770@.z14g2000cwz.googlegroups.com...
>I think it's mostly an audit trail, to see who created the database.
>|||Thanks for the advice. I liked the idea of a dedicated db owner, to
keep things modular, but the orphaned user concern is a good point.
I'll review those areas mentioned and go with sa as long as the
associated points are not a problem.