SQL Server Performance Forum – Threads Archive

allow users to truncate a table

How do I allow users to truncate a table without 1) changing the ownership of the table to that of the user that will be truncating the table or 2) granting db_owner role to the users that will be truncating the table. Option 1 is restrictive in that only one user can truncate the table and it’s just plain stupid. Option 2 grants too many rights to the user who just needs to truncate the table. I have tried creating a procedure to truncate the table in question and granting execute privilege to the user but that did not work. The user still needs to have the db_owner role directly. Thanks,
Jethrojaw

---

Why would you want to allow regular users to truncate tables? What’s the scenario that forces you to allow it?

---

You might find this useful:http://www.sql-server-performance.com/q&a118.asp —
Frank Kalis
SQL Server MVP
http://www.insidesql.de

---

you can give db_ddladmin permissions to regular users.. this is sufficient.. check BOL for more informations.. Permissions
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.
Deepak Kumar –An eye for an eye and everyone shall be blind

---

Jethrojaw You’re inviting trouble in giving such super power access to the users.
Then its better to have a granular auditing to investigate during any troubles in future. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

---

Like Adriaan already said, it would now be interesting to hear, why and what for. There might be a workaround or even better solution. —
Frank Kalis
SQL Server MVP
http://www.insidesql.de

---

Thanks to everyone for their comments and input. I find it so surprising that everyone is so wild up about allowing a user the ability to truncate a table. What if we want to allow a user the ability to refresh a source table interactively. I was hoping to create a procedure within a database that will truncate the table and refresh it with current data. I would then grant execute permission on that procedure to specific users that have the need to perform such task. Unfortunately the SQL Server security setup does not allow truncate table to be inherited by other users via procedure. If it did, then it would be so simple to allow the user or users to perform truncate while being control by the permission to a single procedure. Now I have to think about granting db_owner, db_ddladmin, etc. to the user in order to accomplish this. Also I do not want to use delete since truncate is more efficient for purging the entire table. SQL Server needs to improve its security to allow more flexibility. I know I am opening a can of worm by making that statement because I’m sure many out there think the existing security is grade A. SQL Server doesn’t even provide a standard role that can execute all procedures within a database. It has data_read and data_write, why not have a proc_exec role as well? There are times when users need to truncate a table. Of course you cannot just give it on all tables to everyone out there but I find it hard to believe the huge excitement and passionate outcry this has caused.

---

If you’re giving users the ability to destroy data in your database without logging, why not give them dbo? There are very few worse things a user could do. As for sweeping stored procedure roles, most applications I work with have several maintenance and admin sp’s that I wouldn’t want typical users accessing. I prefer to give Public access to the ‘user’ stored procedures and have typical users in Public. I can’t think of an instance where I would want a user to casually have the ability to truncate tables. If you have scratch tables that are destroyed and repopulated then use temp tables. If you have an aggregate table to speed up analytical processing then why not just delete its contents and refill it?

---

Once a user did this in the wrong moment, you’ll understand why. But in the end it’s your data, your system and your environment. Are you the DBA? If so, see this as a kind of CYA strategy. Who knows what it’s good for anyway? I don’t understand your approach here at all. You say, your users should be able to empty a table on their own. So, we’re talking about a permanent table? What if one user decides to TRUNCATE the table, while another one running a query, or a report against this table?
I disagree here. That’s what temporary tables are for. Truncation might be an automated process, but should never happen at the will of a user. Just my $0.02 —
Frank Kalis
SQL Server MVP
http://www.insidesql.de

---

Thanks again for everyone’s comments and opinions. If everyone agrees on everything, this would be a boring world. I value all the comments and will keep them in mind. We all operate under slightly different business requirements and with different user profiles so there naturally would be differences in operating standards and practices. As for the technical aspect of the question. It seems there is no way to grant the ability to truncate a single table to a database id (user, batch, application, etc.). In order to truncate a table, the database id must be granted the db_owner or db_ddladmin role or something with even more access such as sysdamin. If the technical aspect of this question is not true, please comment. Thank You,
Jethro

---

BOL clearly defines:
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. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

---

]]>