Schema Owner and Name Resolution

I am merging two databases and created two different schemas to contain the objects in the new DB. I would like the objects in both schemas to be visible to one user but I can't figure out how to make this happen. I thought I could just set the Owner of the schemas to the same User but that didn't work. At the moment, the User default schema is set to dbo. I would like the default to be both schemas but this isn't an option.

Is there any way to have multiple schema objects visible to a single user Thanks!

Answer this question

Schema Owner and Name Resolution

BortNE24
Hi,

the schemas are visible to the user if he has access to the objects within. You will have to (and should preferable do this all the time ) use the schema prefix before the object names. the resolution order of objects is (sys --> default schema -- > dbo). A user can only have on e default schema, so setting the default schema would not work in your situation as the objects are spread over two schemas (If I understood you correctly).

HTH; Jens K. Suessmeyer.

---
http://www.sqlserver2005.de
---
JGiers
Make sure, you set the defautl schema of the user you are connecting with to U1 to query the tables of the U1 schema without any prefixes.

HTH, Jens K. Suessmeyer.

---
http://www.sqlserver2005.de
---
Doubleugly
Jens - Thanks for the reply. You did indeed understand my situation. After reading your reply, I thought by changing the Schema owner to that of the User I'd have visibility but that does not appear to work.

For example, I created user U1, two schemas S1 and S2, then tables within each schema while connected as SA. The results are S1.T1, S1.T2, S2.TA1, S2.TA2, etc. Both the Schemas and the Tables have an owner of DBO. When I change the Schema owners to U1, all subordinate tables change owners accordingly (to U1). But when I log in to SQL as U1 and query the tables (without including the schema node), I get the dreaded "Invalid object name '<table>'" error message.

I'm not a real DBA, I just play one at work. I am probably doing something stupid but for the life of me I can't figure out what. The crux of my problem is that I have a legacy application that management would prefer not modifying, therefore I can't go back and prefix the referenced tables with schemas. I was hoping to use schemas to maintain the distinction between the tables from the two separate databases but if I can't make the objects visible to a single user, then I'm out of luck.
Schema Owner and Name Resolution

Answer this question