dbo, who should it be?

Question

dbo, who should it be?

Greg Larsen

SSC-Insane

Points: 20961
More actions
March 21, 2005 at 2:17 pm

#102452

In a shop there are a number of folks in sysadmin server role, basically all the DBAs. Now when you create a database the owner(dbo) of that database by default gets mapped to the DBAs login that creates the databases. Not really a big deal, except when that DBA leaves. So I wonder if there is anyone out there who might say the dbo should always be mapped to SA, or something. I'm trying to determine what are the pros and cons of a login being the owner or having SA be the owner of a database. Any comments will be much appreciated.
Gregory A. Larsen, MVP

Viewing 15 posts - 1 through 15 (of 27 total)

You must be logged in to reply to this topic. Login to reply

hoo-t SSChampion Points: 12713 More actions · Answer 1

Not sure how much help this is, but I vote for sa ownership. We try to ensure that all production databases are owned by sa.

Steve

Yelena Varshal SSC-Dedicated Points: 34286 More actions · Answer 2

It is a big deal not only when a DBA leaves. It is a deal every time you move a database from server to server. I have to use sp_changedbowner every time we move a database when it is created not by SA but by a Windows Authenticated Local Login, as many DBA's tend to work. I would vote for SA. Even a bigger issue when they create objects: tables and views etc. when logged not as a login mapped to dbo or a member of DDLadmin but as someone else. Then the objects have unintended non-dbo ownership which creates a lot of problems. You even can not drop a user if he owns objects and you never know when he leaves where hisusername.hisobject is hardcoded in the applications.

Yelena

Regards,Yelena Varsha

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 3

I vote me. dbo should be me. If you don't have an account for me in your enterprise, create one for me and give me instructions on how to come through your VPN. Okay, seriously...

It probably should be a SQL Server account. SA is the natural choice. In those rare cases where you're using cross-database ownership chaining, you may have to differ, but otherwise, there's no reason not to be sa.

K. Brian Kelley
@kbriankelley

Greg Larsen SSC-Insane Points: 20961 More actions · Answer 4

Kind of thought SA might be the logical answer, but just wanted to know.....Thanks for the input, more welcome if you have it.

Gregory A. Larsen, MVP

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 5

imo the only difference for the "dbo-login" is that (s)he does not have to qualify schema DBO when creating objects. Members of the db-owner db-group always have to specify the dbo when creating objects, otherwize the objects will still be created with their username as schema.

We only set it to a sql-id (sa (default) or myserver_mydb_DBO) and yes, it requiers the extra sp_changedbowner.

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 6

It also matters if you've got to restore a database, SQL Server can't read the sysusers table, and for what ever reason you don't have sysadmin rights. Rare, I admit, but in those cases if you're the database owner but not sysadmin, you can restore from backup. And like, I said, it matters for cross-database ownership chaining. Objects owned by dbo will map as far as chaining is concerned to the login sid of the database owner.

K. Brian Kelley
@kbriankelley

Rudyx - the Doctor SSC-Forever Points: 43695 More actions · Answer 7

'sa' is the logical choice. domain/username database creators/owners have caused minor issues when the AD accounts are removed for the database on it's original server (ownership of 'unknown' or 'null'). Databases restored to other servers are not as much of a concern because there already exists a builtin 'sid' issue that needs to be remedied immediately (unless the master databases are identical. how often is this ?). Then there are application exceptions. We presently have a few vendor applications that require a 'specific' login account to create/own the database (if not the applications do not work). Granted this is not optimal application architecture, but that is the way it is with some vendors. Now all of this may go out the window with SQL 2K5 though ...

RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

Russell Shilling SSCarpal Tunnel Points: 4399 More actions · Answer 8

I just use the db_owner database role for our developers, and all objects that are created are owned by dbo by default.

So long, and thanks for all the fish,

Russell Shilling, MCDBA, MCSA 2K3, MCSE 2K3

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 9

If you're a member of the db_owner role but you don't alias to dbo (like members of the sysadmin role do), the issue is unless you specify the owner on the create statement, the owner won't be dbo.

For instance, if Tommy, who is a member of the db_owner role (and isn't the database owner nor is Tommy a member of the sysadmin role), creates a stored proc like so:

CREATE PROC NotOwnedByDBO
AS
SELECT 1
GO

Then the stored procedure will be created as Tommy.NotOwnedByDBO.

Of course, if ownership is specified, for instance:

CREATE PROC dbo.OwnedByDBO
AS
SELECT 1
GO

All is well.

K. Brian Kelley
@kbriankelley

Scott Arendt SSCrazy Eights Points: 8218 More actions · Answer 10

It's an interesting question. I am in the same boat at the shop that I am at. When I first took over as DBA, I found that our databases are owned by members or the IT Department, developers, whoever did the last install and finally former employees.

I still haven't cleaned up the existing databases, because I can't get agreement for the account to use. But at least all new objects are owned by a single account.

Regards,

Scott

Fred Williams-134192 SSCrazy Points: 2482 More actions · Answer 11

The convention at my previous shop was to create a SQL login with the same name as the database, but with _dbo appended, and sp_changedbowner to that login. Another SQL login, with the same name as the database, was given datareader and datawriter permissions (or sometimes was aliased to dbo). This was the login used by application programs. This convention was adopted mainly because most of our client programs ran under Linux, and could not use Windows authentication. We also usually implemented our own "application user" tables, and let the applications themselves manage "users" and their permissions.

Yelena Varshal SSC-Dedicated Points: 34286 More actions · Answer 12

There is a bigger "theoretical" problem. Most of the developers and sysadmins who may even have a decent T-SQL or Network knowledge confuse Enterprise Manager with SQL Server. In approximatly 60% of cases. They don't see the Enterprise manager as a Front End, the Application that connects to SQL Server by using specified or default authentication. The local server is usually registered by default upon SQL Server installation with Windows Authentication. So most of what is created by them is owned by their Windows login. I normally explain the role of the connection in the Registration Properties to everyone I am working with when installing SQL Servers or client tools or just working with developers. The same goes for Query Analyzer in SQL 2000 (not in 2005) . Query Analyzer in 2000 will open the query window with the authentication that is provided by the context in EM if you open it from EM, not the start menu. So nobody pays attention who is actually connected and creates objects. Don't tell me it is on the title bar, nobody reads.

Yelena

Regards,Yelena Varsha

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 13

There are two issues here, though.

One, with a login per database (as the owner) there is an extra account per database. Since that account is owner of the database, yeah, you guessed it. Bad. That many more potential entry points for an attacker.

Two, you tend to want to restrict permissions against base tables. Using db_datareader and db_datawriter bypasses this. Also, db_datareader has the ability to view all system tables within the database.

K. Brian Kelley
@kbriankelley

Fred Williams-134192 SSCrazy Points: 2482 More actions · Answer 14

Re: One, with a login per database (as the owner) there is an extra account per database. Since that account is owner of the database, yeah, you guessed it. Bad. That many more potential entry points for an attacker.

...One could make the same argument against one-login-per-user...

Re: Two, you tend to want to restrict permissions against base tables. Using db_datareader and db_datawriter bypasses this. Also, db_datareader has the ability to view all system tables within the database.

...We had no desire to restrict permissions against base tables, nor against system tables, although we were considering moving towards an "execute-stored-procedure-only" permission scheme, to prevent poorly written (by app developers) queries...