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]?

  • 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

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

  • Ok, thanks for the additional information. That is quite useful.
    – RLH
    Jul 10 '12 at 18:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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