none
Criação de Trigger DDL RRS feed

  • Pergunta

  • Senhores,

    Alguem poderia me orientar a criar uma trigger onde não seja possivel realizar comandos DDL no banco pelo SSMS pelo usuario TESTE.

    Outros usuarios e aplicação deve ter acesso a esta criação, apenas via SQL Server Management e pelo determinado usuário (teste)não podem criar.

    quarta-feira, 26 de fevereiro de 2014 18:16

Respostas

  • Renata,

    Fiz uma trigger para bloquear a criação de tabelas pelo usuário "TESTE" no contexto do SSMS. Faça os demais ajustes e bloqueios como você precisa.

    CREATE TRIGGER TI_BLOQUEIA_TABLE
    ON ALL SERVER
    FOR CREATE_TABLE
    AS 
    	if (EXISTS(SELECT 1 FROM sys.sysprocesses where spid > 50 and program_name LIKE '%Microsoft SQL Server Management Studio%' and spid = @@SPID and loginame = 'TESTE'))
    	  BEGIN
    		RAISERROR('VOCÊ NÃO TEM PERMISSÃO PARA EXECUTAR ESTA TAREFA !', 10, 1);
    	  END
    GO
    
    --Faça um teste
    CREATE TABLE TB_TESTE (CAMPO1 INT)
    GO
    

    Não esqueça de marcar como resposta todos os posts que ajudaram na sua solução !

    Abraços,

    Durval Ramos
    Microsoft Partner | MTA - SQL Server 2012
    ----------------------------------
    Se foi resolvido clique "Marcar como resposta" e se foi útil "Votar como Útil"
    quinta-feira, 27 de fevereiro de 2014 13:44
    Moderador
  • Renata,

    Fiz uma trigger para bloquear a criação de tabelas pelo usuário "TESTE" no contexto do SSMS. Faça os demais ajustes e bloqueios como você precisa.

    CREATE TRIGGER TI_BLOQUEIA_TABLE
    ON ALL SERVER
    FOR CREATE_TABLE
    AS 
    	if (EXISTS(SELECT 1 FROM sys.sysprocesses where spid > 50 and program_name LIKE '%Microsoft SQL Server Management Studio%' and spid = @@SPID and loginame = 'TESTE'))
    	  BEGIN
    		RAISERROR('VOCÊ NÃO TEM PERMISSÃO PARA EXECUTAR ESTA TAREFA !', 10, 1);
    	  END
    GO
    
    --Faça um teste
    CREATE TABLE TB_TESTE (CAMPO1 INT)
    GO
    

    Não esqueça de marcar como resposta todos os posts que ajudaram na sua solução !

    Abraços,

    Durval Ramos
    Microsoft Partner | MTA - SQL Server 2012
    ----------------------------------
    Se foi resolvido clique "Marcar como resposta" e se foi útil "Votar como Útil"

    Renata,

    Se você pesquisar no Books On-Line sobre Trigger DDL, poderá encontrar alguns exemplos similares a este que o Durval postou.


    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]

    • Marcado como Resposta Giovani Cr terça-feira, 4 de março de 2014 17:31
    sábado, 1 de março de 2014 14:23
  • Durval,

    Acredito que seria necessário colocar um Rollback dentro do seu Trigger!!!!


    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]

    • Marcado como Resposta Giovani Cr terça-feira, 4 de março de 2014 17:31
    sábado, 1 de março de 2014 14:28

Todas as Respostas

  • Renata,

    Neste caso a melhor opção é negar o acesso do usuário TESTE para alteração de objetos do banco de dados. Veja o modelo de script abaixo:

    USE DB_SeuBanco
    GO
    
    DENY ALTER TO [TESTE]
    GO

    Para maiores informações veja o link abaixo:

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

    http://technet.microsoft.com/en-us/library/aa258829(v=sql.80).aspx

    Se ajudou na sua solução, não esqueça de marcar como resposta !

    Abraços,

    Durval Ramos
    Microsoft Partner | MTA - SQL Server 2012
    ----------------------------------
    Se foi resolvido clique "Marcar como resposta" e se foi útil "Votar como Útil"
    quinta-feira, 27 de fevereiro de 2014 01:04
    Moderador
  • Renata,

    Ao invés de criar uma trigger, o que não é nada bom para o ambiente, é mais facil controlar isso por server e database roles.


    Att,
    Marcos Freccia [MTA|MCTS|MCITP|MCT SQL Server 2008]
    Blog|Twitter
    Assine também os feeds clicando aqui

    quinta-feira, 27 de fevereiro de 2014 01:59
  • Eu não posso negar o acesso, porque via aplicação o usuario teste tem que ter permissão pra criar tabela, apenas via SSMS ele nao pode.
    quinta-feira, 27 de fevereiro de 2014 11:55
  • Eu não posso negar o acesso, porque via aplicação o usuario teste tem que ter permissão pra criar tabela, apenas via SSMS ele nao pode.

    Renata,

    Quem tem acesso aos fontes desta aplicação ? Se for apenas um grupo reservado de pessoas, você pode incluir o comando EXECUTE AS para que seu comando ( consulta / procedure / ...) seja executado como outro usuário. Veja abaixo:

    CREATE PROCEDURE Sua_Proc 
    @PARAM1 int
    WITH EXECUTE AS OWNER
    AS
    --...
    GO

    Segue links com maiores informações:

    http://technet.microsoft.com/pt-br/library/ms188354.aspx

    http://msdn.microsoft.com/pt-br/library/ms181362.aspx

    Não esqueça de marcar como resposta todos os posts que ajudaram na sua solução !

    Abraços,

    Durval Ramos
    Microsoft Partner | MTA - SQL Server 2012
    ----------------------------------
    Se foi resolvido clique "Marcar como resposta" e se foi útil "Votar como Útil"
    quinta-feira, 27 de fevereiro de 2014 12:47
    Moderador
  • Renata,

    O SSMS é apenas uma interface para acesso ao SQL Server, a permissão ou trigger são criadas no Database Engine, ou seja, não importa se ele faz algo via SSMS ou via aplicação.


    Att,
    Marcos Freccia [MTA|MCTS|MCITP|MCT SQL Server 2008]
    Blog|Twitter
    Assine também os feeds clicando aqui

    quinta-feira, 27 de fevereiro de 2014 12:56
  • Acho que eu nao me expressei bem.

    O usuario "teste" só pode fazer (create, alter ou drop) pela aplicação, via SQL Server Management NÃO.

    Eu sei que existe uma trigger DDL que bloqueia esta ação, minha unica dificuldade esta em expressar que isso tem que ser bloqueado qdo vem do PROGRAM NAME:  SQL Server Managemen, qualquer outro program name ele deve permitir.

    quinta-feira, 27 de fevereiro de 2014 13:03
  • A trigger DDL, você tem que criar, pois não existe algo do proprio SQL. Existe a função PROGRAM_NAME() que retorna essa informação. Na comparação, você também teria que usar a função SUSER_NAME()

    Agora é só verificar a propria documentação Microsoft http://msdn.microsoft.com/en-us/library/ms189799.aspx

    que você vai aprender a criar essa trigger.

     

    Att,
    Marcos Freccia [MTA|MCTS|MCITP|MCT SQL Server 2008]
    Blog|Twitter
    Assine também os feeds clicando aqui


    • Editado Marcos FrecciaMVP quinta-feira, 27 de fevereiro de 2014 13:09
    • Marcado como Resposta Giovani Cr terça-feira, 4 de março de 2014 17:31
    • Não Marcado como Resposta Renata Cardoso quarta-feira, 12 de março de 2014 17:06
    quinta-feira, 27 de fevereiro de 2014 13:07
  • Renata,

    Fiz uma trigger para bloquear a criação de tabelas pelo usuário "TESTE" no contexto do SSMS. Faça os demais ajustes e bloqueios como você precisa.

    CREATE TRIGGER TI_BLOQUEIA_TABLE
    ON ALL SERVER
    FOR CREATE_TABLE
    AS 
    	if (EXISTS(SELECT 1 FROM sys.sysprocesses where spid > 50 and program_name LIKE '%Microsoft SQL Server Management Studio%' and spid = @@SPID and loginame = 'TESTE'))
    	  BEGIN
    		RAISERROR('VOCÊ NÃO TEM PERMISSÃO PARA EXECUTAR ESTA TAREFA !', 10, 1);
    	  END
    GO
    
    --Faça um teste
    CREATE TABLE TB_TESTE (CAMPO1 INT)
    GO
    

    Não esqueça de marcar como resposta todos os posts que ajudaram na sua solução !

    Abraços,

    Durval Ramos
    Microsoft Partner | MTA - SQL Server 2012
    ----------------------------------
    Se foi resolvido clique "Marcar como resposta" e se foi útil "Votar como Útil"
    quinta-feira, 27 de fevereiro de 2014 13:44
    Moderador
  • Renata,

    Fiz uma trigger para bloquear a criação de tabelas pelo usuário "TESTE" no contexto do SSMS. Faça os demais ajustes e bloqueios como você precisa.

    CREATE TRIGGER TI_BLOQUEIA_TABLE
    ON ALL SERVER
    FOR CREATE_TABLE
    AS 
    	if (EXISTS(SELECT 1 FROM sys.sysprocesses where spid > 50 and program_name LIKE '%Microsoft SQL Server Management Studio%' and spid = @@SPID and loginame = 'TESTE'))
    	  BEGIN
    		RAISERROR('VOCÊ NÃO TEM PERMISSÃO PARA EXECUTAR ESTA TAREFA !', 10, 1);
    	  END
    GO
    
    --Faça um teste
    CREATE TABLE TB_TESTE (CAMPO1 INT)
    GO
    

    Não esqueça de marcar como resposta todos os posts que ajudaram na sua solução !

    Abraços,

    Durval Ramos
    Microsoft Partner | MTA - SQL Server 2012
    ----------------------------------
    Se foi resolvido clique "Marcar como resposta" e se foi útil "Votar como Útil"

    Renata,

    Se você pesquisar no Books On-Line sobre Trigger DDL, poderá encontrar alguns exemplos similares a este que o Durval postou.


    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]

    • Marcado como Resposta Giovani Cr terça-feira, 4 de março de 2014 17:31
    sábado, 1 de março de 2014 14:23
  • Durval,

    Acredito que seria necessário colocar um Rollback dentro do seu Trigger!!!!


    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]

    • Marcado como Resposta Giovani Cr terça-feira, 4 de março de 2014 17:31
    sábado, 1 de março de 2014 14:28