3

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.

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.

  • Yeah, I know that, but TRUNCATE is superior to DELETE if I need to get rid of whole table contents due to speed :) – Evaldas Buinauskas Dec 12 '17 at 13:20
  • then you have no choice but to grant the user the ALTER statement :) – Edgar Allan Bayron Dec 12 '17 at 13:23
6

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
2

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.

  • This seems to be quite an overkill for what I'd like to achieve in my code. Although, I'll keep that in mind! – Evaldas Buinauskas Dec 13 '17 at 9:29
  • @EvaldasBuinauskas This technique does involve more steps than simply adding WITH EXECUTE AS OWNER, so from a setup perspective it looks like overkill, but from a net-effect perspective, it is the most controlled, granular, and secure option. And it probably performs better as well given that using Impersonation (i.e. EXECUTE AS) requires a security context switch each time to swap out the current User, and then change back at the end. Don't worry about the number of steps. It is all quite simple, especially after you do it the first time. – Solomon Rutzky Dec 13 '17 at 18:14
  • Yeah, I agree that there's some boiler plate code here that SQL Server simply needs. It's just that the application that executes this code doesn't run too often and is a batched job that can live using old-good delete statements, performance isn't that critical there. Although even this seems to be an overkill for what I'm doing now, I can see that it could be used in other kind of applications that run within our enterprise. – Evaldas Buinauskas Dec 13 '17 at 20:24
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.