SQL Server Performance Forum – Threads Archive
Deny Truncate TABLE on Table1
Hi, Can I deny the TRUNCATE TABLE permission on particular table? Means If I run the TRUNCATE TABLE on TABLE1. This table should not truncate. The table1 have primary key & foreign key. But Still Truncate Table is working on TABLE1. Amit KumarMob.: 9873812005
Amit
Have a look at the following: ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/3d544eed-3993-4055-983d-ea334f8c5c58.htm It states: 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. This helps
Satya
It is worth remembering that if a table is referenced by a foreign key constraint you cannot use the TRUNCATE TABLE statement on that table. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
]]>