SQL user does not use default schema

So I was troubleshooting an installation of SuperOffice Customer Service. When running the installer it would fail with an ODBC driver error saying something down the line of “’config’ object not found” and you could see it was doing a “select count(*) from config”

I jumped over to the SQL server and saw that the SuperOffice SQL user did not have crm7 schema set as default schema and was not owner. So I chanced that, and tried again. I would still failed. So I open a new query in Management studio and did a “select * from config” .. that should work, but didn’t, I would still get no such object.

after goggling a while I decided to do a alter user, so opened the “help” page for “alter user” and voila, after reading the page I suddenly knew what was wrong.

The value of DEFAULT_SCHEMA is ignored if the user is a member of the sysadmin fixed server role. All members of the sysadmin fixed server role have a default schema of dbo.

The person who had installed superoffice had added the SQL user crm7 to the sysadmin role, and as you can see, that will break the schema role assignment. After removing the user from sysadmin, everything worked again.