No SQL Server, versão 2008R2 Enterprise, 64 bits, criamos um banco de dados para rastrear alterações de DDL.
Fui inspirado por esta solução: https://www.mssqltips.com/sqlservertip/2085/sql-server-ddl-triggers-to-track-all-database-changes/
Em seguida, criamos um gatilho, em cada banco de dados, assim:
CREATE TRIGGER [CaptureChanges] ON DATABASE
FOR CREATE_PROCEDURE, ALTER_PROCEDURE, DROP_PROCEDURE
AS ....
Onde, após o as, o código faz uma simples inserção no banco de dados de auditoria.
A princípio, funciona muito bem, mas quando um usuário, proprietário de um banco de dados, mas sem permissão no banco de dados de auditoria, tenta alterar um armazenado, o SQL Server gera um erro por permissão insuficiente.
Isso está correto, porque o usuário não tem permissões no banco de dados de auditoria.
Então eu usei EXECUTED AS usando um usuário com função sysadmin :
CREATE TRIGGER [CaptureChanges] ON DATABASE
WITH EXECUTE AS 'useradmin'
FOR CREATE_PROCEDURE, ALTER_PROCEDURE, DROP_PROCEDURE
Mas agora não funciona porque este erro:
Msg 916, Level 14, State 1, Procedure CaptureChanges, Line 0 [Batch Start Line 0] The server principal "useradmin" is not able to access the database "userdatabase" under the current security context.
Parece que, quando você usa EXECUTED AS, você é colocado em sandbox para um único banco de dados: http://www.sommarskog.se/grantperm.html#EXECUTE_AS
Então, não posso usar EXECUTED AS, pois não funciona cross db. Eu não gosto da ideia de criar a tabela para acompanhar as alterações em cada banco de dados onde um usuário pode ser dbowner .
Posso atribuir permissão a cada usuário para escrever na tabela de destino, mas preciso lembrar disso para cada novo usuário. Alguém com certeza vai esquecer isso.
Eu também considerei um rastreamento, mas ele pode ser facilmente interrompido ou não será reiniciado na inicialização do servidor.
O gatilho está errado? Posso corrigir isso de alguma forma?
Você pode ter um rastreamento do lado do servidor como um trabalho do agente SQL e configurar o trabalho para iniciar sempre que o agente SQL iniciar e um segundo agendamento para iniciar a cada xx número de minutos. Dessa forma, o trabalho sempre começará e o segundo cronograma cuidará se alguém o interromper por engano.
Sugiro que você crie uma função de banco de dados e torne todos os usuários membros dessa função. Entendo sua preocupação de esquecer de adicionar usuários à função ou atribuir permissão. Pessoalmente, não prefiro ter muitos usuários com privilégios para alterar o esquema no meu banco de dados, portanto, a lista deve ser pequena e uma função facilitará o gerenciamento do privilégio.