locked
Accessing schemas from procedures RRS feed

  • Question

  • Hi Guys,

    I have a scenario,

    I created a database called testdb and in side that I have 2 schemas

    1. Schema1
    2. Schema2

    each schema contains a table called ' table1 ' and 2 separate users are assigned to each of the schema. So the structure is as follows.

    1. Schema1 --- table1 --- user1
    2. Schema2----- table1 ---- user2

    Now I wrote a  procedure and the procedure is as follows. This procedure is created under ' dbo '

    create PROCEDURE [dbo].[testproc]
       
    AS
    BEGIN
       
    execute as user = 'user1'
        SELECT * from t1
    END


    when i executed this procedure, it is giving an error like .' Invalid object name table1 '. I knows that , it is because, the procedure is not able to find the object under 'dbo'. But inside the procedure, i used the statement like  "
    execute as user = 'user1' ", and ' user1 '  is connected to ' schema1 ', so it should select from 'table1' inside ' schema1', right?, then why it is not happening here?

    is there is any alternative for this problem. I don't want to add the schema level qualifier to the query (select * from scheam1.table1)

    Thanks
    Praveen


    Thursday, November 6, 2008 7:53 AM

All replies

  • did you set default_schema for user1 and user2 ?

    http://msdn.microsoft.com/en-us/library/ms173463.aspx 

    Thursday, November 6, 2008 8:48 PM
  • ya, i already created the default schema for both the users and after that only i wrote this procedure.
    Friday, November 7, 2008 4:15 AM
  • Hi Praveen,

     

    This issue has been raised earlier too and we already have a task item tracking this issue. You can look at this link for more details

    http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=272964

     

    For now, you can use dynamic-sql like this:

     

    CREATE PROCEDURE [dbo].[testproc]

    WITH EXECUTE AS 'user2'

    AS

    BEGIN

    Declare @sql nvarchar(max)

    select @sql = 'select * from '+ '['+SCHEMA_NAME()+']'+'.[table1]'

    exec(@sql)

    END

     

    exec dbo.testproc

     

    Friday, November 7, 2008 8:48 AM
    Answerer
  • I mean did you map these schemas to users?

    Friday, November 7, 2008 10:34 AM
  • These schemas have been made default schemas of the users. If this is the mapping you are asking for then yes these schemas are mapped to the users.

    Friday, November 7, 2008 6:01 PM
    Answerer
  •  

    Thanks a lot for the Microsoft Connect link Sumesh.

     I have a minor correction to the sample code included in order to avoid SQL injection:

    Code Snippet
    select @sql = 'select * from ' + quotename(SCHEMA_NAME()) + '.[table1]'

     

     

     

      If no dynamic SQL is really needed, I would strongly recommend avoiding it. In the case of the original SP, it can be rewritten with a two part name (schema.object) in order to avoid the use of dynamic SQL:

     

    Code Snippet

    create PROCEDURE [dbo].[testproc]

    AS

    BEGIN

      execute as user = 'user1'

      SELECT * from [schema1].[t1]

    END

     

     

     

     BTW. I noticed the usage of an ad-hoc EXECUTE AS call before the select statement, this implies that the caller has explicit permission to impersonate user1 (who may have higher permissions that the caller). If the caller doesn’t have impersonate permission on “user1” the call will fail and abort the batch execution.

     

       This is just an observation, since I am sure the code here is just a demo snippet and not the real code, therefore I have no context to tell you if this is the intended behavior.

     

      Thanks,

    -Raul Garcia

      SDE/T

      SQL Server Engine

    Friday, November 7, 2008 6:59 PM