Stored procedures and schemas???

• fernandolopez_fjlf

  SSC Enthusiast

  Points: 110

  More actions

  May 14, 2009 at 8:38 am

  #137822

  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

  SSCrazy

  Points: 2576

  More actions

  May 14, 2009 at 9:18 am

  #994620

  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.

  [font="Comic Sans MS"]+++BLADE+++[/font]:cool:

• fernandolopez_fjlf

  SSC Enthusiast

  Points: 110

  More actions

  May 14, 2009 at 2:39 pm

  #994907

  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