Criação de Trigger DDL

Question

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.

Answer 1

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"

Answer 2

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

Answer 3

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.

Answer 4

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"

Answer 5

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

Answer 6

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.

Answer 7

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

Answer 8

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"

Answer 9

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]

Answer 10

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]