question

JHamilton avatar image
JHamilton asked

Why can't I get the default schema setting to work with a Windows user or group?

We are running into an issue with an install, receiving the error "Invalid object name ...". It does not seem to be using the default schema assignment set on the Windows user. I have set up a test in our AdventureWorks2012 database and am receiving the same result. When I connect to the instance with the Windows account (set up with a default schema of "Person" in the AdventureWorks2012 database), and execute the statement `SELECT * FROM Address`, I receive the same error "Invalid object name Address". Next I created a SQL login, mapped the login to the AdventureWorks2012 database and again set the default schema to "Person". When I connect to the instance using the SQL login, and execute the command `SELECT * FROM Address`, the query is successful and returns results. I am experiencing the same outcome in both SQL 2012 and SQL 2014. Can anyone help me out with why the default schema setting is not working with the Windows user?
sql-server-2012schemadefault
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
JHamilton avatar image
JHamilton answered
I was able to resolve my issue through further testing. The Windows user also had been granted the database role of db_)owner. That role assignment was forcing the default schema to dbo, rather than the default schema assigned to the user. Once I removed the membership of the db_owner role, the query then used the default schema and completed successfully.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.