locked
Problema em Stored Procedure usando EXECUTE AS RRS feed

  • Pergunta

  • Pessoal

    eu possuo uma stored procedure no schema dbo, mas quando estou executando ela queria que ela pegasse as informações do contexto que a conexão está. 

    Eu tenho 2 tabelas com mesmo nome e schemas diferentes, dbo e teste, quando efetuo o "execute as user = 'teste'" e despois acesso dbo.sp_teste as informações que pego são das tabelas do schema dbo.

    Já tentei efetuar "execute as user" dentro da procedure e não consegui. Verifiquei que se eu acessar a função "user" ele me mostra o usuário do schema que eu queria. 

    Se alguém puder ajudar fico grato.

    Segue o código que usei para testes:

     

     

    create table teste.TABELA (a int)

    insert into teste.TABELA values(1),(2), (3)

     

    create table dbo.TABELA (a int)

    insert into dbo.TABELA values(4),(5), (6)

     

     

    execute as user = 'teste'

     

    create procedure dbo.sp_teste

    as

    begin

    select * from TABELA 

    select user

    end

     


    • Editado Célio3 sexta-feira, 20 de janeiro de 2012 19:34
    • Movido Gustavo Maia Aguiar segunda-feira, 23 de janeiro de 2012 01:07 (De:SQL Server - Desenvolvimento Geral)
    sexta-feira, 20 de janeiro de 2012 19:00

Respostas

  • Célio,

         O comportamento default do SQL sempre procura pelo Schema Default do usuário e depois no Schema DBO. O que está acontecendo com a sua procedure em termos gerais é:

         Para o usuário User_DBO:

         No momento de executar a proc, o SQL verifica o Schema do Usuário, DBO.

         Com base nisso, ele procura nas stored procedures, uma proc que se chame dbo.sp_teste.

         Depois de encontrado, ele executa a proc.

     

         Para o usuário teste:

          No momento de executar a proc, o SQL verifica o Schema do Usuário, TESTE.

          Com base nisso, ele procura nas stored procedures, uma proc que se chame teste.sp_teste.

          Ele não encontra essa proc, então o SQL procura no schema DBO por uma proc chamada dbo.sp_teste.

          Encontra a proc e executa a proc.

          Neste momento, ele já está executando no schema DBO.

          Por este motivo, ele está procurando sempre na sua tabela dbo.tabela

       Existem três formas de resolver esse problema:

        1 - Ao invés de encapsular seu select numa stored procedures, você utilizar o SELECT diretamente.

        2-Criando duas procs, dbo.sp_teste e teste.sp_teste

        3-Usando uma query dinâmica. (Que eu não recomendo por diversos motivos (busque mais informações aqui mesmo na lista), mas fica a seu critério)  

           Eu criei um script que pode ajudar, não que seja algo que eu possa me orgulhar, mas, enfim, resolve o problema:

    create procedure dbo.sp_teste
    as
    begin
       DECLARE @String NVARCHAR(2000)
       SET @String = N'select * from ' + CONVERT(NVARCHAR(30), schema_name()) + N'.TABELA'
       SELECT @String
       EXECUTE (@String) 
       select user
    end
    
    execute as user = 'teste'
    EXEC sp_teste
    REVERT
    execute as user = 'User_dbo'
    EXEC sp_teste
    
    


    Espero ter ajudado

     


    Roberto Fonseca MCT / MCITP - Database Administrator 2008 MCITP - Database Developer 2008 MCITP - Business Intelligence 2008

    domingo, 22 de janeiro de 2012 02:38
    Moderador

Todas as Respostas

  • Celio,

     

    Não entendi direito, como assim sua tabela possui 2 schemas? Isso não é possivel, voce pode ter tabelas com o mesmo nome em schemas diferentes, mas ainda sim, tabela diferentes.


    Fabrizzio A. Caputo
    MCT
    Certificações:
    Oracle OCA 11g
    MCITP SQL Server 2008 Implementation and Maintenance
    MCITP SQL Server 2008 Developer
    Blog Pessoal: www.fabrizziocaputo.wordpress.com
    Blog Empresa: www.tripletech.com.br/blog
    Twitter: @FabrizzioCaputo
    Email: fabrizzio.antoniaci@gmail.com
    sexta-feira, 20 de janeiro de 2012 19:03
    Moderador
  • Me desculpe,

    eu possuo 2 tabelas com nomes iguais, mas cada uma no seu schema.

    eu queria que minha procedure fosse do schema dbo e a cada mudança de contexto ela teria de ser capaz de identificar a tabela de cada schema.

    seria:

    execute as user = 'teste'

    ele retornaria os dados da tabela teste.tabela

    execute as user = 'dbo'

    ela retornaria os dados da tabela dbo.tabela

    Só que isto não acontece, quando eu acesso uma procedure no schema dbo todos selects de tabelas não são feitos no schema do meu "execute as user"  

    O que acontece comigo:

    execute as user = 'teste'

    ele retorna os dados da tabela dbo.tabela

    execute as user = 'dbo'

    ela retorna os dados da tabela dbo.tabela

    Lembrando este select estaria dentro da procedure dbo.sp_teste

     

    sexta-feira, 20 de janeiro de 2012 19:31
  • Celio,

     

    E se você usar  EXECUTE AS OWNER ?

     

    Veja se funciona !


    Robson Brandão
    sexta-feira, 20 de janeiro de 2012 21:20
  • Célio,

    Outra possibilidade seria tentar o Execute as Login passando o login SA.


    Pedro Antonio Galvão Junior [MVP | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados | SorBR.Net | Professor Universitário | MSIT.com]
    domingo, 22 de janeiro de 2012 01:15
    Moderador
  • Célio,

         O comportamento default do SQL sempre procura pelo Schema Default do usuário e depois no Schema DBO. O que está acontecendo com a sua procedure em termos gerais é:

         Para o usuário User_DBO:

         No momento de executar a proc, o SQL verifica o Schema do Usuário, DBO.

         Com base nisso, ele procura nas stored procedures, uma proc que se chame dbo.sp_teste.

         Depois de encontrado, ele executa a proc.

     

         Para o usuário teste:

          No momento de executar a proc, o SQL verifica o Schema do Usuário, TESTE.

          Com base nisso, ele procura nas stored procedures, uma proc que se chame teste.sp_teste.

          Ele não encontra essa proc, então o SQL procura no schema DBO por uma proc chamada dbo.sp_teste.

          Encontra a proc e executa a proc.

          Neste momento, ele já está executando no schema DBO.

          Por este motivo, ele está procurando sempre na sua tabela dbo.tabela

       Existem três formas de resolver esse problema:

        1 - Ao invés de encapsular seu select numa stored procedures, você utilizar o SELECT diretamente.

        2-Criando duas procs, dbo.sp_teste e teste.sp_teste

        3-Usando uma query dinâmica. (Que eu não recomendo por diversos motivos (busque mais informações aqui mesmo na lista), mas fica a seu critério)  

           Eu criei um script que pode ajudar, não que seja algo que eu possa me orgulhar, mas, enfim, resolve o problema:

    create procedure dbo.sp_teste
    as
    begin
       DECLARE @String NVARCHAR(2000)
       SET @String = N'select * from ' + CONVERT(NVARCHAR(30), schema_name()) + N'.TABELA'
       SELECT @String
       EXECUTE (@String) 
       select user
    end
    
    execute as user = 'teste'
    EXEC sp_teste
    REVERT
    execute as user = 'User_dbo'
    EXEC sp_teste
    
    


    Espero ter ajudado

     


    Roberto Fonseca MCT / MCITP - Database Administrator 2008 MCITP - Database Developer 2008 MCITP - Business Intelligence 2008

    domingo, 22 de janeiro de 2012 02:38
    Moderador