database owner?

[aj]

SQL Server 2005 SP2

What is the significance of the owner of a database being sa as
opposed to some administrative (or non-administrative) local login?

Should I favor one over the other?

Can I change the owner of a database?

TIA

aj

[Erland Sommarskog]

Re: database owner?

aj (ronald@mcdonal ds.com) writes:

SQL Server 2005 SP2
>
What is the significance of the owner of a database being sa as
opposed to some administrative (or non-administrative) local login?
>
Should I favor one over the other?

That depends on what the server and databases are used for. If you run a
web host, you probably want to make the clients owners of their own
databases.

If you are a DBA who runs a server with a couple of applications on them,
and you are responsible for them all, sa should be just fine.

A special consideration is if you need to enable cross-database chaining.
That is, if a user calls a procedure SP1 in database A, and SP1 accesses
objects in database B, and you don't want to the user to have direct
access to those objects, you need to enable DB chainging for these
databases, and you need make sure that A and B have the same owner. If
you then have have a second pair C and D, you may want to have different
owners for C and D, so that chaining is not possible from A to C.

Can I change the owner of a database?

ALTER AUTHORIZATION ON DATABASE::db TO someuser

If I remember the syntax correct. Else check Books Online for details.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Dan Guzman]

Re: database owner?

What is the significance of the owner of a database being sa as

opposed to some administrative (or non-administrative) local login?

I agree with Erland that 'sa' is the best choice unless you have a reason to
do otherwise. I posted some of my other thoughts on the subject at

http://weblogs.sqlteam.com/dang/archive/2008/01/13/Database-Owner-Troubles.aspx

--
Hope this helps.

Dan Guzman
SQL Server MVP

http://weblogs.sqlteam.com/dang/

"aj" <ronald@mcdonal ds.comwrote in message
news:to-dnehcNIIKl83VnZ 2dnUVZ_vednZ2d@ supernews.com.. .

SQL Server 2005 SP2
>
What is the significance of the owner of a database being sa as
opposed to some administrative (or non-administrative) local login?
>
Should I favor one over the other?
>
Can I change the owner of a database?
>
TIA
>
aj