Menu
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.
283
To resolve it, you could run the command as Execute Immediate. Change your stored procedure to call the TRUNCATE statement like this: CREATE PROCEDURE testProc IS BEGIN EXECUTE IMMEDIATE 'TRUNCATE TABLE tablename'; END testProc; This procedure should now run successfully.
When you want to Truncate the table just 'DROP' the table en copy 'MyTable_Template' to 'MyTable'. Now the autoincrementfield of your 'new' table 'MyTable' will start at 1 again.
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.
121
You can create a stored procedure with execute as owner:
create procedure dbo.TruncTable with execute as owner as truncate table TheTable go Then grant execution permissions to whoever needs to truncate that table:
grant execute on TruncTable to TheUser Now TheUser can truncate the table like:
exec dbo.TruncTable If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
