CODEWITHSUNDEEP
sqlsql-server-2008
user171523
user171523

Reputation: 4365

Truncate table permissions

What are min permission we need to truncate table ? Apart from DDLAdmin. And what is best pratice to give permission to the user to truncate the user on SQL 2008 R2

Upvotes: 4

Views: 6727

Answers (3)

gbn
gbn

Reputation: 432421

If you don't want to grant rights (which are excessive, really, and described in other answers) you can escalate permissions within a stored procedure...

CREATE PROC DoTruncate
WITH EXECUTE AS OWNER
AS
TRUNCATE TABLE Mytable
GO

And permission this instead with "normal" rights

Upvotes: 2

YetAnotherUser
YetAnotherUser

Reputation: 9356

MSDN:

The minimum permission required is ALTER on table_name. TRUNCATE TABLE permissions default to the table owner, members of the sysadmin fixed server role, and the db_owner and db_ddladmin fixed database roles, and are not transferable.

Upvotes: 2

Phil Murray
Phil Murray

Reputation: 6554

Truncate table documentation at books online here

Permissions

The minimum permission required is ALTER on table_name. TRUNCATE TABLE permissions default to the table owner, members of the sysadmin fixed server role, and the db_owner and db_ddladmin fixed database roles, and are not transferable. However, you can incorporate the TRUNCATE TABLE statement within a module, such as a stored procedure, and grant appropriate permissions to the module using the EXECUTE AS clause. For more information, see Using EXECUTE AS to Create Custom Permission Sets.

Upvotes: 4

Related Questions