Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
ai12345
Starting Member
2 Posts |
 Posted - 2010-07-24 : 15:40:17
|
| HI all,Im trying to find a way to change a users login session schema only for the duration of the connection.So for instance a database "db", has 3 schemas, all with the same table name:schematest1.productsschematest3.productsschematest2.productsUser "test_user", has no default schema, but has default databse "db". So when I connect using this login and execute the followingselect * form products --- it fails!!!I want to know is it possible, im sure it is, as in Oracle to alter the session schema, so it qualifies the select without the prefix required.I know it is possible to change the defualt schmea, using "Alter USER...". But i just need to change it for the duration of login session. Changes to the database are not permitted.Any ideas guys???? |
|
|
jeffw8713
Aged Yak Warrior
819 Posts |
Posted - 2010-07-25 : 13:34:09
|
| You really should consider schema qualifying the code, instead of trying to manipulate the schema being used. In SQL Server, doing it your way will cause the system to generate a separate execution plan for each user that calls your code.Without a default schema defined for the user, SQL Server is going to look to the 'dbo' schema - that is why it fails.Finally, I don't know of any way to set a session default schema in SQL Server. If you really think you need this, you can put your request on connect. |
 |
|
|
|
|
|
|
|
SQL Server changing login session schema???