Default schema not being referenced RRS feed

  • Question

  • Here's a scenario:

    I have two application roles:

    StagingRole with default schema as Staging
    ProductionRole with default schema as Production

    I have tables in both schemas that are the same, as an example tblMyData

    Consider the following:

    DECLARE @cookie varbinary(8000);

    EXEC sp_setapprole 'StagingRole', 'Staging', @fCreateCookie = true, @cookie = @cookie OUTPUT;

    select * from tblMyTable;

    exec sp_unsetapprole @cookie;

    The above shows data in the Staging.tblMyTable.  All is great.  This is what I would expect.  If I set the application role to ProductionRole, I get the Production.tblMyTable results.

    Now, I put the select statement in a stored procedure that is in the dbo schema as such:

    Create Stored Procedure GetMyTableData
    WITH Execute as Caller
        select * from tblMyTable;

    Now cosider this script:

    DECLARE @cookie varbinary(8000);

    EXEC sp_setapprole 'StagingRole', 'Staging', @fCreateCookie = true, @cookie = @cookie OUTPUT;

    Exec dbo.GetMyTableData;

    exec sp_unsetapprole @cookie;

    I gave the application roles execute permission.  The procedure executes, but I get an error saying that tblMyTable does not exist.  I have checked the User_Name within the sproc and it is correctly changed to execute as the caller.  However, it is as if SQL Server is ignoring the default schema of the caller and is unable to resolve the object tblMyTable.

    Am I using the default schema incorrectly here?  BOL seems to imply that this should work as this is the behavior of the new property Default Schema.

    Just to relay what I am trying to solve...I want to have a common set of stored procedures that execute DML on tables that exist in either a staging or production schema.  This way, I am not maintaining literally two copies of procs. 

    thanks in advance, all.

    Tuesday, December 23, 2008 10:14 PM

All replies