SQL Server Permission to Truncate Table

database-designddlsql server

How Do I give permission for user to Truncate all tables in database?

The minimum permission required is 'ALTER on table_name' per articles below. With this, how do I give permission to allow user to Alter All tables in a database, so they can truncate? Is it something like for 'sp_MSforeachtable' or other method?

Thanks,

What permissions are necessary for truncating a table?

https://www.mssqltips.com/sqlservertip/4112/minimum-permissions-for-sql-server-truncate-table/

We have SQL Server 2016 Enterprise

Best Answer

Please do not grant any permission to any User outside of EXECUTE on a particular Stored Procedure that will do what you are wanting. Granting ALTER on a Table or Schema allows that User to make any change they want. You want them to be able to TRUNCATE, but granting them ALTER to a Table will allow the User to add / remove / change Columns, etc.

You want to create a Stored Procedure to contain only the specific actions that you feel are permissible. Whether that is a loop that goes through all tables and executes TRUNCATE on each one (will reflect new and removed Tables; might need a pattern specific for the Table name, perhaps?), OR is a list of accepted Tables to be truncated (won't reflect new and removed Tables, but maybe new Tables shouldn't be included anyway), is up to you. Either way, you should retain full control over the scenarios in which the User can perform this action.

Fortunately, this is fairly simple to accomplish. All you need to do is:

  1. Create the Stored Procedure
  2. Grant EXECUTE on the Stored Procedure to the User(s) and/or Role(s) that should be able to perform the TRUNCATE
  3. Create a Certificate
  4. Create a User from the Certificate
  5. Grant the User any permissions needed to perform this action and/or add the User to any necessary fixed Database-Roles needed to perform this action.
  6. Sign the Stored Procedure with the Certificate

The following is the T-SQL that corresponds to the steps noted above:

-- 1) Create the Stored Procedure:
GO
CREATE PROCEDURE dbo.[SomeTable_Truncate]
AS
SET NOCOUNT ON;

    TRUNCATE TABLE dbo.[SomeTable];
GO

-- 2) Grant EXECUTE on the Stored Procedure to the User(s) and/or Role(s) 
--    that should be able to perform the TRUNCATE.
GRANT EXECUTE ON dbo.[SomeTable_Truncate] TO [SomeUserOrRole];

-- 3) Create a Certificate
CREATE CERTIFICATE [Permission$AlterSchema]
    ENCRYPTION BY PASSWORD = 'choose_a_password'
    WITH SUBJECT = 'Allow for TRUNCATE TABLE',
    EXPIRY_DATE = '2099-12-31';

-- 4) Create a User from the Certificate
CREATE USER [Permission$AlterSchema]
    FROM CERTIFICATE [Permission$AlterSchema];

-- 5) Grant the User any permissions needed to perform this action and/or add the User
--    to any necessary fixed Database-Roles needed to perform this action.
GRANT ALTER ON SCHEMA::[dbo] TO [Permission$AlterSchema];

-- 6) Sign the Stored Procedure with the Certificate.
--    Repeat this step as necessary.
ADD SIGNATURE
    TO dbo.[SomeTable_Truncate]
    BY CERTIFICATE [Permission$AlterSchema]
    WITH PASSWORD = 'choose_a_password';

That's it.

Related Question