Owning a Database: The Blueprint Upgrade Conundrum

Introduction

Nothing spoils a great weekend like a Change Request. And nothing spoils a spoiled weekend faster than running into problems during the Change.

 Lately, there has been a rise of failed upgrades to Blueprint Enterprise, largely fueled by improper permissions to the database for the Login Account used for Blueprint operation. This document serves to talk about some common ideas about database ownership in Microsoft SQL, dispel some myths, and ultimately explain how this applies to upgrade events in Blueprint Enterprise.

The 'dbo vs db_owner' Issue

The first part of the confusion stems from "dbo" versus "db_owner" and how SQL addresses each from a security standpoint. The explanation itself contains twists in language, so I will try to be very clear. It has to do with logins versus roles, and a dash of archaic history in the relationship of users and schemas.

What is dbo?

The acronym "dbo" literally means database owner. Each database hosted in SQL Server has to be owned by one (and only one) login, and this login gets aliased to the dbo login. The dbo user (and its aliases) cannot be denied, which means that specific login can do anything they want to within the database. Not the server, just the database.

"dbo" can also mean "schema" and this is where a lot of people get tripped up. Back in the dark ages of SQL Server (version 2000 and lower), creating a login also created a schema for the login, and the two were inseparable. So people who administered databases awhile ago have this idea that "dbo" is almost like the "sa" (system administrator) login, and so balk at the use of database ownership tied to a login. But in version 2005, Microsoft changed that. A schema in SQL Server 2005 and newer is just a collection of objects in the database: tables, indexes, etc. that can be owned and accessed by users, and "dbo" is the default schema when an alternate isn't defined. So if I am a login that can, for example, create a table in a database and I simply run:

CREATE TABLE MyStuff (
Id int,
Type varchar(25),
Description varchar(100)
)

the object will be placed into the dbo schema, resulting in dbo.MyStuff. The owner of the dbo schema in SQL has a lot of power only because most of the objects in each database are in the dbo schema. If I built a database that had a schema named "Gandalf" and put all of the database objects in that schema, then the owner of the Gandalf schema would have more access in the database than the owner of the dbo schema. But ownership of a schema doesn't imply extended or administrative permissions. You can just see the stuff in the database.

In conversations with database administrators, it is very important to provide the proper context for dbo: are you talking about the schema (collection of objects) or the login (the database owner)?

What is db_owner?

In SQL, db_owner is a fixed database-level role that has explicit permissions within the database. Logins can be added to the db_owner role for the purpose of database administration. According to Microsoft's documentation, logins assigned the db_owner role (whose permissions cannot be changed) to the database are pretty much free to do anything (anything) in the database (again, not the server, just the database). However, membership in the db_owner role does not confer dbo user permissions. And so, any T-SQL script that needs to act under dbo needs to be run as the dbo of the database.

So What Does That Mean for Blueprint?

Operationally (meaning: after installation), the account specified for the databases in the Blueprint Server Configuration utility can happily live as a member of the db_owner fixed role. During an upgrade, however, and to retain backwards compatibility with previous SQL versions, there is a check to ensure that the specified login is also dbo. If there was not, then Blueprint installations on older SQL Server versions would fail, and by doing so, the potential exists to cause failed upgrades, particularly on servers managed by security-conscious database administrators who seek to keep "least privileged" access at all times (and so often change database ownership to restricted domain-based accounts with tight access controls).

Removing the Fault

In almost all cases, Blueprint server upgrades are scheduled events, typically managed as part of a Change Control process. Therefore, within the change control procedure, implement a Database task to temporarily change the dbo alias to the account used by the Blueprint Analyst to access the database:

USE psbprint [psjobs1, psjobs2, psjobs3, psjobs4..., psreports]
GO
sp_changedbowner 'login'
GO

where the action is taken on the various Blueprint databases (in turn) and login is replaced by the login account name. NOTE: in some future version of SQL Server, the sp_changedbowner procedure will be removed. Use ALTER AUTHORIZATION instead:

ALTER AUTHORIZATION ON DATABASE::psbprint TO login;

Then, when the update is complete, the change control procedure gets another Database task to move the dbo alias back to whatever is used for production purposes, using the same syntax/script above. This restores the "least access" privilege.

And That's It!

In sum, being the dbo isn't all bad; its exactly the same as being a member of the db_owner fixed role, without some of the hassle associated with an upgrade. However, with some planning, the upgrade experience can go smoothly; well, except for the ruined weekend part.