Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Stored procedures and schemas??? Expand / Collapse
Author
Message
Posted Thursday, May 14, 2009 8:38 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, November 01, 2012 7:08 AM
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
Post #717020
Posted Thursday, May 14, 2009 9:18 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Monday, June 01, 2009 6:06 AM
Points: 518, 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+++
Post #717062
Posted Thursday, May 14, 2009 2:39 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, November 01, 2012 7:08 AM
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
Post #717406
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse