x

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?

more ▼

asked Jun 23 at 07:31 PM in Default

avatar image

JHamilton
1 1 2

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

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.

more ▼

answered Jun 28 at 11:12 PM

avatar image

JHamilton
1 1 2

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x385
x43
x13

asked: Jun 23 at 07:31 PM

Seen: 45 times

Last Updated: Jun 28 at 11:12 PM

Copyright 2017 Redgate Software. Privacy Policy