CODEWITHSUNDEEP
sql-serverschemadefault
Jacki H
Jacki H

Reputation: 21

Default schema not working with Windows user

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?

Upvotes: 1

Views: 2405

Answers (1)

Jacki H
Jacki H

Reputation: 21

Resolved my issue. It appears that the membership in the database role db_owner also defaults to dbo schema as once I removed the db_owner database role the default schema assignment kicked in and the query completed successfully.

Thank you to all that considered my issue.

Upvotes: 1

Related Questions