SA for database ownership?

Question

SA for database ownership?

DavidKBennett

Right there with Babe

Points: 752
More actions
January 23, 2012 at 3:03 pm

#254045

The other DBA here left for greener pastures and I've finding all the things that were default named with his login. I thought I had them all and then found out that replication broke because he had been listed as owner on a number of databases. I have been changing the ownership of all DBs to SA, so that no matter who comes or goes we don't have this problem again.
Is there any problem any of you know with making the SA account the owner of databases or other objects?
On most of my instances, login to the SA account is disabled, but it still seems to take ownership just fine.
We are running various versions from 2000 (soon to be retired!) to 2008 R2.
Thanks,
Dave Bennett

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

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

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 1

Database owner defaults to database creator.

I prefer not to have "sa" as the owner of the databases but what I call a "operational account" for such database meaning that different databases on the same server have different owners most of the time. The same "operational account" can be used to own jobs, etc. but never for user access to the system.

This practice preserves the sanctity of "sa" account and also dettaches databases from actual people that may come and go while databases stay put.

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 2

I don't know if I agree Paul, my policy is that ALL databases are owned by sa and that no one should have the sa password. I don't ever use it and no one else should either. It is stored in case it is ever needed but the password is not given out.

In this context sa really doesn't have negatives. I don't allow for cross-db chaining which might be one reason to have different ownership. For a multi-tenant operation maybe. For jobs that I want to run in the context of the server (which is many of them, if not most) they are owned by sa as well. I don't want users to own anything, in the case where I might change ownership, I would create a user for that task and document WHY I departed from the standard that I set.

I want to insulate myself from the cases where an admit leaves and things start breaking because I killed their login..

CEWII

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 3

I'll second all of that, Elliott. I'll also add that I prefer that most things are in the "dbo" schema.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 4

Jeff Moden (1/26/2012)
I'll second all of that, Elliott. I'll also add that I prefer that most things are in the "dbo" schema.

I tend to code to that as well. But I can see some reasons to use schemas. I'll let a developer go down that path if they can justify it.

CEWII

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 5

Elliott Whitlow (1/26/2012)
I don't know if I agree Paul, my policy is that ALL databases are owned by sa and that no one should have the sa password. I don't ever use it and no one else should either. It is stored in case it is ever needed but the password is not given out.
In this context sa really doesn't have negatives. I don't allow for cross-db chaining which might be one reason to have different ownership. For a multi-tenant operation maybe. For jobs that I want to run in the context of the server (which is many of them, if not most) they are owned by sa as well. I don't want users to own anything, in the case where I might change ownership, I would create a user for that task and document WHY I departed from the standard that I set.
I want to insulate myself from the cases where an admit leaves and things start breaking because I killed their login..
CEWII

I'm okay with dissagreement 🙂

Having said that, please note that as stated my "operational accounts", the ones that own each database are not owned by a human users, nobody uses them, nobody in the user/developer community even know they exist and, nobody but DBA knows the passwords which are documented and kept in a sealed envelope. "Operational accounts" are a layer to put 'sa' one more step away from the misseries of human life 😉

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

DavidKBennett Right there with Babe Points: 752 More actions · Answer 6

All,

Thanks for the responses. I guess I'm going to continue to go with sa for my ownership. I already have way too many accounts to keep track of for people (and resources) that actually log in. It sounds like there is no real downside to using sa for database ownership (which is what I wanted you'all's opinion on) so I will keep on with it.

Thanks for the feedback!

Dave

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 7

PaulB-TheOneAndOnly (1/27/2012)
I'm okay with dissagreement 🙂

As am I. Especially on something like this.

CEWII

SQLDCH SSChampion Points: 11630 More actions · Answer 8

I'm having to deal with this now (database ownership) and came across a SQL Server 2005 best practice doc from Microsoft, stating that databases should not be owned by 'sa' (page 18). I'm guessing that the reasoning is similar to Paul's.

My environment is exactly like David's ('sa' account exists but is disabled). Management will make me adhere to Microsoft's ideas so a separate, "operational" account will have to be used.

----------------------------------------------------------------------------
Sacramento SQL Server users group - http://sac.sqlpass.org
Follow me on Twitter - @SQLDCH
----------------------------------------------------------------------------

Yeah, well...The Dude abides.

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 9

SQLDCH (2/6/2012)
I'm having to deal with this now (database ownership) and came across a SQL Server 2005 best practice doc from Microsoft, stating that databases should not be owned by 'sa' (page 18). I'm guessing that the reasoning is similar to Paul's.
My environment is exactly like David's ('sa' account exists but is disabled). Management will make me adhere to Microsoft's ideas so a separate, "operational" account will have to be used.

Careful now. It doesn't simply state that "databases should not be owned by 'sa'. It states...

Have distinct owners for databases; not all databases should be owned by sa.

... and it says that in a section where individual customers own and can control their own databases in shared system which is a very specific requirement. Making the blanket statement that "databases should not be owned by 'sa'" isn't the right thing to do especially after you've misquoted the "official" MS documentation (heavily disclaimered by MS in the front matter, BTW). That's how really bad SQL "myths" get started. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 10

Jeff,

What MS doc is this? It sounds like best practices for a shared hosting environment. In THAT environment I would agree with not being owned by sa, but for my company databases...

CEWII

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 11

Elliott Whitlow (2/7/2012)
Jeff,
What MS doc is this? It sounds like best practices for a shared hosting environment. In THAT environment I would agree with not being owned by sa, but for my company databases...
CEWII

SQLDCH had provided a link in his post immediately previous to mine in the word "doc" and it's hard to see that it's actually a link. Here's the full link...

http://www.google.com/url?sa=t&rct=j&q=microsoft%20sql%20server%20best%20practice&source=web&cd=1&ved=0CDcQFjAA&url=http%3A%2F%2Fdownload.microsoft.com%2Fdownload%2F8%2F5%2Fe%2F85eea4fa-b3bb-4426-97d0-7f7151b2011c%2FSQL2005SecBestPract.doc&ei=7WswT7fnG5THsQL6vPSRDg&usg=AFQjCNFv2JoFJvPxIp1ArMuUJQSSwKKC4g

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 12

Thanks Jeff. The paragraphs before it mentions not using sa specifically talks about IT department mode vs ISV mode. I don't think you can take sa shouldn't be used from this document unless the context is ISV. And in the ISV use case I agree. However, lets be real, most of us are not running SQL in that mindset..

CEWII

SQLRNNR SSC Guru Points: 281334 More actions · Answer 13

Elliott Whitlow (2/7/2012)
Thanks Jeff. The paragraphs before it mentions not using sa specifically talks about IT department mode vs ISV mode. I don't think you can take sa shouldn't be used from this document unless the context is ISV. And in the ISV use case I agree. However, lets be real, most of us are not running SQL in that mindset..
CEWII

I agree here. I think that statement is meant more for ISV. In a shared environment, I would have separate operational accounts as described by Paul.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 14

SQLRNNR (2/7/2012)
Elliott Whitlow (2/7/2012)
Thanks Jeff. The paragraphs before it mentions not using sa specifically talks about IT department mode vs ISV mode. I don't think you can take sa shouldn't be used from this document unless the context is ISV. And in the ISV use case I agree. However, lets be real, most of us are not running SQL in that mindset..
CEWII
I agree here. I think that statement is meant more for ISV. In a shared environment, I would have separate operational accounts as described by Paul.

That's why I said that saying that "databases shouldn't be set to 'SA'" was a bad thing. 😛

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)