SQL Server TRUNCATE TABLE permissions

Question

Is it possible to configure SQL Server user so that he doesn't have access to DDL statements but has access to run TRUNCATE TABLE commands. I know that behind the scenes it's a DDL.

I wasn't able to find this on search engines.

Answer 1

You could use this technique by Jonathan Kehayias to create a stored procedure that will allow the user to TRUNCATE a table.

As Johathan says:

"You can't grant truncate because for whatever reason, that permission doesn't exist. What you can do is use a stored procedure and use EXECUTE AS OWNER to get around it."

Here is the sample code from the above post.

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

Answer 2

It is not possible to grant TRUNCATE without granting ALTER to the user.

Though you can have a work around by granting DELETE to the user.

Answer 3

I would suggest using module signing to accomplish this since, in the end, it is both the most granular and the most secure option. This is similar to the Impersonation option suggested by @Scott in that it wraps the desired functionality in a Stored Procedure, but it is less problematic than using EXECUTE AS since it doesn't change the security context, and the elevated permissions get dropped if someone changes a single byte of the Stored Procedure, forcing you to review the change and only re-apply the permissions if you agree to the changes.

Just do the following:

1. Create a Stored Procedure that does the TRUNCATE TABLE and whatever else needs to be done.

2. In the DB in question, create a Certificate (my preference is to specify a password instead of relying upon the Database Master Key (DMK) for the protection):

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

3. Sign the Stored Procedure with the Certificate using ADD SIGNATURE:

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

4. Back up the Certificate to:

   • files (a .cer file for the Public Key a.k.a. "Certificate", and a .pvk file for the Private Key):

     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'
     );
     

   OR:

   • VARBINARY literals using built-in functions:

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

5. Remove the Certificate's private key using ALTER CERTIFICATE to prevent anything else from being signed with the Certificate (though not a high risk since they would also need the password):

   ALTER CERTIFICATE [TruncatePermission]
       REMOVE PRIVATE KEY;
   

6. Create a User from that Certificate:

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

7. Grant that User whatever permissions are necessary to accomplish the goal. Try to find the least privileged / most restrictive permission that works, but in this case it might require "dbo" (via the db_owner fixed Database Role):

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

8. Grant EXECUTE on the Stored Procedure to whatever combination of Users and/or Roles should be able to perform this operation.

The permissions granted to the Certificate-based User are a bit much, but that User cannot be impersonated (i.e. EXECUTE AS USER = N'Mr.Truncate';) and cannot log in; that User is merely a container for the extra permissions, and those only ever apply to what has been signed with that Certificate, which in this case is just the one Stored Procedure.

If you ever need to change the code within that Stored Procedure, or grant those permissions to another module via signing with ADD SIGNATURE, then you will need to either create that same Certificate in another DB if the new code to sign is in another DB, or you will need to restore the Private Key into this Certificate using the ALTER CERTIFICATE statement.

Answer 4

You need permission to ALTER table.

You can find it here: https://technet.microsoft.com/en-us/library/ms177570(v=sql.105).aspx

under: Permissions.