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???


  • you can always change the db owner through the sql server management console. Probably easier to do it there than trying to figure out a query for it.
    – Marc B
    Jan 22, 2012 at 1:17
  • That is where i tried to change it
    – Malcolm
    Jan 22, 2012 at 2:10
  • Are you issuing DDL or DML?
    – gbn
    Jan 22, 2012 at 7:59

1 Answer 1


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

... 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)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.