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?
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.
17 People are following this question.
