SQL Server 2008 default schema not being respected?
Solution 1
Has foo user sysadmin rights on SQL Server? Because if it does then any sysadmin user will have by default assigned schema dbo, independently of what is set in User's properties for a specific database.
So, in order to have a default schema a user needs to have database roles like db_owner, db_datawriter and so on, instead of server role sysadmin.
Tell me if it'snot your case.
Solution 2
Another scenario in which this problem may occur (SQL 2008 R2)...
I had a user linked to a SQL Authentication Login, that I was trying to associate with an existing schema. The Login did not have sysadmin rights, so the above solution did not help.
I eventually figured out that the database was still configured in SQL 2000 compatibility mode. Changing to SQL 2005 resolved the issue.
Related videos on Youtube
John Cromartie
I'm just trying to figure out what to do with this rather strange talent I have of telling computers what to do. I like using it to make things that make people happy. Some people have payed me to use it to make computers spy on their employees, but I didn't feel so good about it. :p
Updated on September 17, 2022Comments
-
John Cromartie almost 2 years
We just copied a SQL 2008 database over to another server, for testing purposes. The typical user that logs in uses some objects in a certain schema. Let's say it's user
foo
and schemabar
.foo
's default schema isbar
at both the instance and database level. However, whenfoo
logs in it can't find any objects in thebar
schema without using a fully qualified name.Why wouldn't the default schema work here?
-
John Cromartie over 13 yearsWe have configured the default schema for
foo
on the new server. It is set asbar
on both the instance user and the database user level. -
Jens over 11 yearsYou are a god and I bow to your knowledge, I was puzzled until I bumped into this!
-
tbone almost 5 yearsStrangely, I seem to be having this problem (user linked to a SQL Authentication Login, that I was trying to associate with an existing schema), except my compatibility level is 'SQL Server 2016 (130)'