Inspired by an actual customer scenario: what if you have a legacy app that doesn’t schema-prefix its database objects, but you want it to work with a specific assigned schema? There’s a quick and easy solution.

I recently came across an application that I had to migrate from Oracle to run on SQL Server. I’m certainly no expert on Oracle, but it seems Oracle folks are not so fond of schemas the way we are on SQL Server. Specifically, this app would use tables and views completely without a schema prefix. Which would have been just fine if it could live in the dbo schema.

I could obviously have gone through the code and made some 1200 code replacements in each and every place where a table or view was mentioned, but I guess I’m just too lazy.

Enter the default schema.

You can assign a default schema to database users. This means that if your user references database objects without specifying a schema, SQL Server will assume the default schema. Here’s how:

ALTER USER [the_user] WITH DEFAULT_SCHEMA=[favorite_schema];

But there’s a catch.

If your user is a database owner, (i.e. is a member of the db_owner group or has CONTROL permissions on the database) the default schema will always be dbo. This is something you can’t change.

So if your legacy application needs quasi-administrative privileges in the database, you can’t make it a database owner, but you can grant those permissions on the schema instead (which is actually a better idea anyway).

Granting CONTROL on the schema gives the user complete an unlimited access to all objects in that schema, so the app can still create, drop or truncate tables, among other things:

GRANT CONTROL ON SCHEMA::[favorite_schema] TO [the_user];

Needless to say, if your app does not need to do DDL stuff, you would be even better off just giving it INSERT, UPDATE, DELETE, SELECT on the schema.

Want to learn more on SQL Server security, take a few minutes to read through my series on principals, securables, permissions and context.