Permissões de TRUNCATE TABLE do SQL Server

Você pode usar essa técnica de Jonathan Kehayias para criar um procedimento armazenado que permitirá ao usuário TRUNCATEuma tabela.

Como diz Johathan:

"Você não pode conceder truncar porque, por qualquer motivo, essa permissão não existe. O que você pode fazer é usar um procedimento armazenado e usar EXECUTE AS OWNER para contornar isso."

Aqui está o código de exemplo do post acima.

CREATE DATABASE foo
GO

CREATE LOGIN foobar
    WITH password = 'Pa$$w0rd';
GO

USE foo
GO

CREATE user foobar
FROM LOGIN foobar;
GO

CREATE TABLE test (rowid INT identity)
GO

INSERT INTO test DEFAULT
VALUES;
GO

SELECT *
FROM test
GO

CREATE PROCEDURE dbo.truncate_test
    WITH EXECUTE AS OWNER
AS
TRUNCATE TABLE test
GO

GRANT EXECUTE
    ON dbo.truncate_test
    TO foobar
GO

EXECUTE AS LOGIN = 'foobar'

EXECUTE dbo.truncate_test

REVERT
GO

SELECT *
FROM test
GO

USE master
GO

DROP DATABASE foo

DROP LOGIN foobar

Não é possível conceder TRUNCATE sem conceder ALTER ao usuário.

Embora você possa ter uma solução alternativa, concedendo DELETE ao usuário.

Eu sugeriria usar a assinatura de módulo para fazer isso, pois, no final, é a opção mais granular e mais segura. Isso é semelhante à opção de representação sugerida por @Scott , pois envolve a funcionalidade desejada em um procedimento armazenado, mas é menos problemático do que usar EXECUTE AS, pois não altera o contexto de segurança e as permissões elevadas são descartadas se alguém alterar um único byte do Procedimento Armazenado, forçando você a revisar a alteração e somente reaplicar as permissões se concordar com as alterações.

Basta fazer o seguinte:

1. Crie um procedimento armazenado que faça o TRUNCATE TABLEe o que mais precisa ser feito.

2. No banco de dados em questão, crie um certificado (minha preferência é especificar uma senha em vez de confiar na chave mestra do banco de dados (DMK) para a proteção):

   CREATE CERTIFICATE [TruncatePermission]
   ENCRYPTION BY PASSWORD = 'password-123'
   WITH SUBJECT = 'Grant temporary db_owner status';
   

3. Assine o procedimento armazenado com o certificado usando ADD SIGNATURE:

   ADD SIGNATURE
     TO dbo.[{new_Stored_Procedure_name}]
     BY CERTIFICATE [TruncatePermission]
     WITH PASSWORD = 'password-123';
   

4. Faça backup do certificado para:

   • (um arquivo .cer para a Chave Pública, também conhecido como "Certificado", e um arquivo .pvk para a Chave Privada):

     BACKUP CERTIFICATE [TruncatePermission]
     TO FILE = 'path_to_public_key_(.cer)_file'  
     WITH PRIVATE KEY
     (
         FILE = 'path_to_private_key_(.pvk)_file',
         ENCRYPTION BY PASSWORD = 'password-123',
         DECRYPTION BY PASSWORD = 'password-123'
     );
     

   OU :

   • VARBINARYliterais usando funções internas:

     SELECT
       CERTENCODED(CERT_ID(N'TruncatePermission')) AS [Cert/PublicKey],
       CERTPRIVATEKEY(CERT_ID(N'TruncatePermission'),
                      'password-123', 'password-123') AS [PrivateKey];
     

5. Remova a chave privada do certificado usando ALTER CERTIFICATEpara evitar que qualquer outra coisa seja assinada com o certificado (embora não seja um alto risco, pois eles também precisariam da senha):

   ALTER CERTIFICATE [TruncatePermission]
       REMOVE PRIVATE KEY;
   

6. Crie um usuário a partir desse certificado:

   CREATE USER [Mr.Truncate] FROM CERTIFICATE [TruncatePermission];
   

7. Conceda a esse usuário quaisquer permissões necessárias para atingir a meta. Tente encontrar a permissão menos privilegiada / mais restritiva que funcione, mas neste caso pode exigir "dbo" (através da db_ownerfunção de banco de dados fixa):

   ALTER ROLE [db_owner] ADD MEMBER [Mr.Truncate];
   

8. Conceda EXECUTEo Procedimento Armazenado a qualquer combinação de Usuários e/ou Funções que possa realizar esta operação.

As permissões concedidas ao usuário baseado em certificado são um pouco demais, mas esse usuário não pode ser representado (ou seja, EXECUTE AS USER = N'Mr.Truncate';) e não pode fazer login; esse Usuário é apenas um container para as permissões extras, e essas apenas se aplicam ao que foi assinado com aquele Certificado, que neste caso é apenas um Stored Procedure.

Se você precisar alterar o código nesse procedimento armazenado ou conceder essas permissões a outro módulo por meio de assinatura com ADD SIGNATURE, precisará criar esse mesmo certificado em outro banco de dados se o novo código a ser assinado estiver em outro banco de dados ou precisará restaurar a Chave Privada neste Certificado usando a ALTER CERTIFICATEinstrução.

Você precisa de permissão para a tabela ALTER.

Você pode encontrá-lo aqui: https://technet.microsoft.com/en-us/library/ms177570(v=sql.105).aspx

em: Permissões.