Alter failed for schema 'dbo' when trying to change schema owner

Question

I have some MS Access code that uses ODBCDirect to execute some sql statements in a SQL 2008 R2 DB.

The sql statements do not specify the schema name so subsequently are executed using the windows user as the schema name and not the result im looking for. The schema should be dbo.

All users belong to a Windows group which is a SQL login.

I read that if I change the owner of the schema dbo to the windows group that sql statemenets that do not specify a schema will default dbo.

So I tried to change owner of the schema to the windows group but get message

Alter failed for schema 'dbo'

I do seem to change alter permission on schemas in the DB as well.

Does sql allow you to change the owner if so how???

Malcolm

Answer 1

You can't change the owner of "dbo" because it is a system schema. From ALTER AUTHORIZATION, "Special Cases and Conditions":

SCHEMA
... Cannot change the owner of sys, dbo, or information_schema

And you can't set a default schema for windows logons (yet).

This matters in 2 ways:

• you are issuing DDL (CREATE TABLE) etc when you want objects to be in dbo
• you won't have reusable query plans for DML without schema so different users will have different query plans

Basically, you have two options:

• fix the queries
• stop using ODBCDirect from MS Access to SQL Server.

The third option, ugly and dangerous, is to make the Windows logon "sysadmin" which means they will always map to dbo. I don't recommend this

Personally, I don't use the dbo schema any more: see dba.se (and the further links there)