Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Yesterday I asked this question regarding changing the dbo of multiple databases that I have. The change makes sense, but I want to be clear.

Is there any, good reason or circumstance why I should not set the dbo of a database to [sa]?

share|improve this question
1  
FYI: When changing the owner of a database, or particularly when you apply this to a bunch of databases at once, make sure your script includes provisions to re-associate database users with their logins. Make this change during off-peak if possible. –  Jon Seigel Jul 11 '12 at 3:06
add comment

1 Answer

up vote 13 down vote accepted

Making SA the owner of a database actually simplifies and/or solves a number of things, but can have some security implications.

In particular, remember that if SA is the owner of a database, then dbo = 'SA'. This means that, among other things, any procedures in the [dbo] schema (which is the default) that have "EXECUTE As Owner" in them, are actually executing as SA. That's not quite as bad as it sounds, because unless you've marked the database as TRUSTWORTHY, SQL Server will not let a session or task out of the database with an impersonated server-level principal like that.

Which brings up the next point: never mark such databases as TRUSTWORTHY, unless you're really, really sure that it is secure. Because anyone with the ability to create procedures in the [dbo] schema can execute as SA, on the entire server, if they want to.

Another issue can come up because many products and applications that have their own SQL Server database, often specify that their application login has to be the DBO of the database. Obviously you could resolve that by making their application login be 'SA'. Hopefully, it's also obvious that you should never do that, unless that SQL Server Instance is not used for anything else (even then, I would recommend against it).

share|improve this answer
    
Ok, thanks for the additional information. That is quite useful. –  RLH Jul 10 '12 at 18:00
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.