Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question

migrated from stackoverflow.com Jan 22 '12 at 13:04

This question came from our site for professional and enthusiast programmers.

    
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 '12 at 1:17
    
That is where i tried to change it –  Malcolm Jan 22 '12 at 2:10
    
Are you issuing DDL or DML? –  gbn Jan 22 '12 at 7:59
add comment

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

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.