SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

Being Careful with Schema Ownership

By Steve Jones,

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. 

 
Total article views: 904 | Views in the last 30 days: 166
 
Related Articles
FORUM

Wrong Schema Owner

Wrong Schema Owner

FORUM

Table creation with schema owner of current login windows ID.

I want:Schema owner should be dbo instead of windows login id at the time to creating any object.

FORUM

Issues creating tables with table owner as DBO

Issues creating tables with table owner as DBO

FORUM

Schema Sandbox

Establish developer sandbox using schemas

FORUM

Create procedure permission in schema

Create procedure permission in schema

Tags
schema    
security    
 
Contribute