none
default schema not working?

    Question

  • I am confused.

    I added my NT account to the sql server logins for my sql server (2005), then I added a corresponding user account to my database.  I then set my default schema.  I connect to the database, and the default schema seems to be set to dbo.

    Can anyone thing of a reason why this might be happening?  Is there some sort of override if I have additional privledges on the server?

    I appreciate any thoughts...

    -Mike Graham

    Tuesday, March 14, 2006 3:50 PM

Answers

  • If you are a sysadmin, you are also a member of db_owner, so your default schema will be dbo.

    The BOL quote refers to the fact that you cannot set a default schema for a database principal that is mapped to a Windows group, NOT that you cannot set it for a database principal who is mapped to a Windows account that belongs to some group.

    So, what prevented the default_schema setting from working was the fact that you were a sysadmin.

    Thanks
    Laurentiu

    Tuesday, March 14, 2006 7:11 PM
    Moderator

All replies

  • AH HA !!!

    In the BOL, it says "you cannot change the default schema for a user that is mapped a windows group" - my account was in the administrators group which had been added to the logins for the sql server. 

    I remove the group and it started working without even closing the query window.

    YES !!!!!!!!!!!!

    Tuesday, March 14, 2006 4:34 PM
  • Ok - little more info:

    I also noticed that if i am in the server role: sysadmin, the default schema assignment doesn't work, but if I remove myself, then it works.

    Tuesday, March 14, 2006 4:44 PM
  • If you are a sysadmin, you are also a member of db_owner, so your default schema will be dbo.

    The BOL quote refers to the fact that you cannot set a default schema for a database principal that is mapped to a Windows group, NOT that you cannot set it for a database principal who is mapped to a Windows account that belongs to some group.

    So, what prevented the default_schema setting from working was the fact that you were a sysadmin.

    Thanks
    Laurentiu

    Tuesday, March 14, 2006 7:11 PM
    Moderator