Re: Database ownership best practice

• Previous message: Noone: "Re: Convert SQL logins to Windows authentication"
• In reply to: stevester_at_freeuk.com: "Database ownership best practice"
• Next in thread: steve: "Re: Database ownership best practice"
• Reply: steve: "Re: Database ownership best practice"
• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]

Date: Sat, 6 Aug 2005 12:58:20 -0500

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.
> 

• Previous message: Noone: "Re: Convert SQL logins to Windows authentication"
• In reply to: stevester_at_freeuk.com: "Database ownership best practice"
• Next in thread: steve: "Re: Database ownership best practice"
• Reply: steve: "Re: Database ownership best practice"
• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]