Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Stored procedures and schemas???


Stored procedures and schemas???

Author
Message
fernandolopez_fjlf
fernandolopez_fjlf
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 53
Hi!
Does anyone know if it is possible to write a Stored Procedured and execute it from different schemas (using its particular tables)?
For example:
I have two tables with the same name over different schemas:

* db1.User (id int, name varchar(50), surname varchar(50))
* db2.User (id int, name varchar(50), surname varchar(50), email varchar(100))

and the following procedure:
CREATE PROCEDURE sp_getusers
AS
BEGIN
SELECT * FROM User
END

If I execute the sp_getusers procedure from a login with default_schema db1, I´d like the procedure to return the User table from that schema not the one from the procedure´s schema.
But if I logged in to SQL Server with default_schema db2, I´d like the procedure to return the second table.
I wonder if this is posible without recompiling each procedure on each schema.
Thanks in advance.
Regards,

Fernando
BLADE-1043594
BLADE-1043594
Mr or Mrs. 500
Mr or Mrs. 500 (520 reputation)Mr or Mrs. 500 (520 reputation)Mr or Mrs. 500 (520 reputation)Mr or Mrs. 500 (520 reputation)Mr or Mrs. 500 (520 reputation)Mr or Mrs. 500 (520 reputation)Mr or Mrs. 500 (520 reputation)Mr or Mrs. 500 (520 reputation)

Group: General Forum Members
Points: 520 Visits: 354
I think you can re-write the sp

include a variable that will accept schema name. and grant permission for the users using the particular schema to access the object user.

variable.user must give the answer i guess.

+++BLADE+++Cool
fernandolopez_fjlf
fernandolopez_fjlf
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 53
Thanks for your answer.
I´been doing some research and I finally arrived at the conclusion this cannot be done unless you use dynamic sql. There´s an option (WITH EXECUTE AS) when you create a procedure that lets you impersonate another user but only when used together with dynamic sql.
It seems this is a kind of workaround for SQL Server 2000 limitation, when you grant a user execution rights over a procedure but no over a table.
Thanks again.
Regards,

Fernando López Frezza
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search