Multiple Schema Permissions In SQL Server 2008 R2

0
votes

We are migrating a database from SQL Server 2000 to SQL Server 2008 R2 and have run into a problem with our schemas. In SQL Server 2000, if you didn't prefix your object names with a owner/schema (e.g. SELECT * FROM blah rather than dbo.blah) SQL Server would still figure it out. This worked even if your object was in a non-dbo owner.

In SQL Server 2005 this was changed as described here: http://blogs.msdn.com/b/mssqlisv/archive/2007/03/23/upgrading-to-sql-server-2005-and-default-schema-setting.aspx.

I can get around a database having 2 schemas by making a user's default schema xyz instead of dbo. The problem I run into is in a database where there is a 3rd schema (abc.blah). When I make the users default xyz, they can get to dbo without a hitch but get an Invalid Object error when trying to select something from abc without a schema prefix (abc.blah works fine).

Of course, it shouldn't have been coded this way to begin with but that is a different battle. I've verified that permissions are not an issue.

I'm sure others have run into this. Has anyone found a workaround other than fixing the code?

1
sqlsql-server-2008-r2

1 Answers

2
votes

There isn't a whole lot of magic here; fix the code.

Assuming you don't have same-named objects in both schemas, you may be able to create a slew of synonyms like:

CREATE SYNONYM xyz.foo FOR dbo.foo;

...but that is going to be as much of a mess as fixing the code.