Not every developer group uses schemas, but they are handy separation boundaries for security or function reasons. Each schema has an owner, which is a user in the database.

There are no inherent problems with having users as owners, except in a long term security standpoint. Employees and users may come and go from development projects, or not even exist in downstream environments, such as production. As a result, we need to either move the schema to a new owner, which can be a source of deployment problems.

This article examines how owners relate to schemas and how you can ensure there are not security or deployment issues in your software development pipeline.

The Default Owner

If you usually connect as a sysadmin to your development databases, you are often mapped as dbo. You can see this in the following code:

If I create a schema, then I find the owner of the schema will be dbo. That's fine, and deployments go smoothly. Even personnel changes are good since I never remove dbo from my database.

What if a developer connects? I've got a developer, SallyDev, that is a user in my development database with the CREATE SCHEMA and CREATE TABLE permissions. I have executed this code:

CREATE USER SallyDev FOR LOGIN SallyDev
GO
GRANT CREATE SCHEMA TO SallyDev
GRANT CREATE TABLE TO SallyDev
GO
 

Now I connect as SallyDev (notice the lower right) and I build a schema and table.

I can now query sys.schemas to find out the ownership structure. Here's what I find. The SallyDev schema is owner by SallyDev. If Sally moves to a new project, or doesn't have an account in production, this might cause me issues.

What if I make SallyDev a ddl_admin member? Let's do that. First, some schema/object cleanup and then I'll change roles. I'll run this as the dbo.

Next I can connect as SallyDev and recreate the schema and table. This is the same code as above. After doing that, let's query the schema and object metadata. As we can see, the schema owner is still SallyDev.

Fixing the Schema Owner From the Start

I can certainly move the schema owner, and I've blogged about that. However, I want to avoid that issue. That's really rework, which is something we want to avoid. Instead, we want the correct schema owner from the start. To do that, I'll use an optional parameter on the CREATE SCHEMA DDL command.

When I do this, I get an error. Why? Even as a db_ddladmin, a developer cannot change the dbo schema. This is a security issue and you should tread carefully here. If you have a set schema owner you'd like to use, such as AppOwner, you can do this:

GRANT IMPERSONATE ON User::AppOwner TO SallyDev 

and then create the schema and tables:

This works fine. However, for dbo, I need to understand if I grant impersonate to dbo for a developer, then I'm essentially making them a db_owner with full control on the schema. If you are ok with that, I'd recommend a couple things. First, use a role for developer rights, and then add the rights to the role so you can remove developers from the role.

CREATE ROLE Developer
ALTER ROLE db_owner ADD MEMBER Developer
 

Then we can let SallyDev create the schema.

Conclusion

I tend to allow developers to be db_owner in their development environments. I do this so they can have complete control over their environment without any blockers. I also want developers to work in a sandbox, so if they do break something, it is their own system, not any shared database. By allowing this, I can then have the code move into shared integration databases, QA, production, whatever without issues.

If you work in this environment, this can be helpful. If not, what I'd do is ensure that either the a db_owner always moves the owner of the schema or you have db_owner move the owner of the schema.  Either works, though I prefer the former since this removes a blocker from developers gettting work done.

As I mentioned, be careful with granting impersonate or db_owner to users, since this implies full control on the database.