What permission do I need to GRANT a user, in MSSQL, in order to be able to truncate a table?
I'm trying to grant the minimal set of permissions, but I can't use DELETE, because the table is very large, and I want the operation to be quick.
What permission do I need to GRANT a user, in MSSQL, in order to be able to truncate a table?
I'm trying to grant the minimal set of permissions, but I can't use DELETE, because the table is very large, and I want the operation to be quick.
You need the ALTER permission: see the Permissions section here.
Note that you can also use a stored procedure with EXECUTE AS, so that the user running the stored procedure does not need to even be granted the ALTER permission.
Source
You can create a stored procedure with execute as owner to only one table or a store procedure to any table:
You can create a stored procedure
with execute as owner
:Then grant execution permissions to whoever needs to truncate that table:
Now
TheUser
can truncate the table like:Don't GRANT, hide...
SET CONTEXT_INFO
may beis without any doubt better to separate a normal DELETE from a TRUNCATE TABLEI haven't tried this...
Edit: changed to use SET CONTEXT_INFO.