Stored procedures and schemas???

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Administering » Stored procedures and schemas???

Stored procedures and schemas???

Rate Topic

Display Mode

Topic Options

Author

Message

fernandolopez_fjlf

Posted Thursday, May 14, 2009 8:38 AM

Forum 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

BLADE-1043594

Posted Thursday, May 14, 2009 9:18 AM

Mr 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+++ Cool

Post #717062

fernandolopez_fjlf

Posted Thursday, May 14, 2009 2:39 PM

Forum 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 »

Permissions