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 |
vamsidhar.tangutoori
Starting Member
19 Posts |
Posted - 2013-07-22 : 12:24:03
|
Hi,I am trying to build a stored procedure reference two diffrent tables with same name but diffrent schemas based on the login_user or who calls the procedure. I have developed the following procedure but still ot able to get itThe table test_table exists two times within the systemUser1.test_TableUser2.TestTableif i am user1 calling the procedure should reefernce user1.test_table and vice versa for user 2. Can anyone please help mealter procedure dbo.test_proc (@pUsername varchar(150))with Execute as calleras BeginSelect System_User Select * from Test_TableEnd |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-07-22 : 12:29:08
|
depends on whats the default schema set for the user. When you do not reference the schema name in select it will try to retrieve table from default schema of the user.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
vamsidhar.tangutoori
Starting Member
19 Posts |
Posted - 2013-07-22 : 12:41:23
|
Hi Visakh,The default schema for the users is diffrentUser1 defaul schema user1user2 default schema user2but still when i execute it the procedure throws error invalid object name test tablequote: Originally posted by visakh16 depends on whats the default schema set for the user. When you do not reference the schema name in select it will try to retrieve table from default schema of the user.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-07-22 : 13:02:37
|
That may be because you executed it for second user. In that case table name is different as per your posting (User2.TestTable as against Test_Table used in the query)------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
vamsidhar.tangutoori
Starting Member
19 Posts |
Posted - 2013-07-22 : 13:06:20
|
User_2 is the schema can we have a way so that procedure will select the schema based on the user the table name is still Test_Table but diifrent schemasquote: Originally posted by visakh16 That may be because you executed it for second user. In that case table name is different as per your posting (User2.TestTable as against Test_Table used in the query)------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs
|
|
|
|
|
|