patternsqlMinor

SQL Server TRUNCATE TABLE permissions

Submitted by: @import:stackexchange-dba·Mar 10, 2026·

Viewed 0 times

permissionssqltruncateservertable

Problem

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.

Solution

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

Code Snippets

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

Context

StackExchange Database Administrators Q#192920, answer score: 6

Revisions (0)

No revisions yet.