SQL Permission to Truncate Table

Question

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

Solomon Rutzky · Accepted Answer · 2018-03-01T04:18:29.083

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:

Create the Stored Procedure
Grant EXECUTE on the Stored Procedure to the User(s) and/or Role(s) that should be able to perform the TRUNCATE
Create a Certificate
Create a User from the Certificate
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.
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.

score 1 · Answer 2 · answered Feb 28 '18 at 09:26

The simplest solution would be to grant the permission at the database level so all objects within the database are affected:

GRANT ALTER ON DATABASE::[DatabaseName] TO [User];

The issue here is that the user now gets ALTER on all objects. Thats possibly undesirable.

Another step down would be to grant it at the schema level:

GRANT ALTER ON SCHEMA::[dbo] TO [User];

This has the same problems as granting at the database level with the advantage its limited to the schema only - not everything in the database. So you could put all tables in their own schema and do it this way.

As was commented by @sepupic wrapping this up in a procedure is probably your best approach. That way you can add any further logic you wish and control exactly what operations can be performed.

John K. N. · Answer 3 · 2018-02-28T16:18:28.927

The easiest solution might be to create a new database role and grant the delete permissions to that role. This way you will only have to assign the delete permisions to the single database_role once (and when you add new tables) and then assign the users that require the delete permissions to this database role.

1. Create New Database Role

Create a new database role and assign it to a fixed database role:

USE <database_name>
GO
CREATE ROLE table_deleter AUTHORIZATION dbo

_Instead of dbo you could use for example db_securityadmin_

This will create a new role in your database. Feel free to use a different name than my suggested table_deleter.

2. Assign Permissions to Database Role

After you have created the new database role, you will have to grant the role permissions to delete data from your talbes. This is done for each table:

USE <database_name>
GO
GRANT DELETE ON OBJECT::<table_name> TO table_deleter
GO
GRANT DELETE ON OBJECT::<table_name2> TO table_deleter
....

3. Add User to Database Role

Once you have granted the permission to delete data on all of your tables, all you have to do is assign the users to this database role.

USE <database_name>
GO
ALTER ROLE table_deleter ADD MEMBER <one_of_the_users>

Advantages

If you want to revoke the permissions in the future for one single user, it's as easy as setting off this command:

USE <database_name>
GO
ALTER ROLE table_deleter DROP MEMBER <one_of_the_users>

But I Wanted to Allow The User to Truncate Tables

If you really want to assign the TRUNCATE permissions to individual users (or to the new database role), then you will have to assign the minimum permissions as you previously mentioned in your question.

2. Assign User The ALTER Permission alternate solution

Instead of my second step where you grant individual DELETE permissions to each table, you would assing the higher privileges to the database role:

USE <database_name>
GO
GRANT ALTER ON DATABASE::<database_name>

...and then go from there.