Faking the dbo role

Long-time users of SQL Server will be familiar with the dbo user. This is a special user that's present in all databases, and represents the database owner - regardless of who that is. Another special thing about it is that all users of the server role System Administrators will be mapped to this user as well. A third special thing is that unlike all other objects, those owned by dbo don't need to be qualified with the owner (think schema) name before they are accessed.

Now, there are a lot of legacy applications out there that depend on this behavior. To make those work, you often have to deal with these three issues. There are obviously a lot of other things to deal with as well, but those are not on the topic of this post.

The first part is easy. Just GRANT the dbo role to the users as needed.

The third part is also easy - use the public schema instead. Make sure that the dbo role has permissions to write to this schema. This will work as long as the application doesn't schema-qualify the names. If it does, you'll need to create a schema called dbo and grant permissions on it to the dbo role.

I've previously had problems with the second part, until I tried something that's actually fairly obvious once you think about it. But I never did before, so here goes. When user joe logs in, even if you grant him CREATE permissions on the public schema, anything he creates will end up owned by joe and not dbo. To get around this, just run the following command for all users:

ALTER USER joe SET ROLE dbo;

That way, whenever joe logs in, the current role will be automatically switched to dbo, and all objects created will be properly owned by dbo.


Conferences

I speak at and organize conferences around Open Source in general and PostgreSQL in particular.

Upcoming

Pycon Sweden
May 9-10, 2016
Stockholm, Sweden
PGCon
May 17-21, 2016
Ottawa, Canada
Stockholm PUG 2016/3
Jun 16, 2016
Stockholm, Sweden
PGDay UK 2016
Jul 05, 2016
London, UK
PG Day'16 Russia
Jul 6-8, 2016
St Petersburg, Russia
Stockholm PUG 2016/4
Aug 31, 2016
Stockholm, Sweden
Postgres Open
Sep 13-16, 2016
Dallas, USA
PGConf.EU 2016
Nov 1-Jan 4, 2016
Tallinn, Estonia

Past

Stockholm PUG 2016/2
Apr 26, 2016
Stockholm, Sweden
PGConf.US
Apr 18-20, 2016
New York, USA
pgDay Paris
Mar 31, 2016
Paris, France
Nordic PGDay
Mar 17, 2016
Helsinki, Finland
ConFoo
Feb 24-26, 2016
Montreal, Canada
More past conferences