TRUNCATE table permissions, Large number of tables

Question

So as a DBA I have inherited a app where lots of TRUNCATE TABLE statements are being used in the stored procedures, I could find around 40 different tables being mentioned on TRUNCATE statement in one database itself on various sp's.
Currently I have given ALTER TABLE permissions on those tables, but i am trying to remove that permission.

Replacing TRUNCATE by DELETE could be one of the options, but given the large number of sps that use truncate, I think there could be some slowness there. Plus there is a reluctance to make a code change on so many sp's from app, but I am looking into it.

Next, I have looked into the EXECUTE AS option, the testing that I have done - I don't think the EXECUTE AS OWNER can be used in between the sp code - Looks like it has to be mentioned at the top , which I don't want to do as I was trying to just use the EXECUTE AS only on the TRUNCATE module statements - The TRUNCATE statements are scattered all over the sp.

This works -

ALTER PROCEDURE [dbo].[truncate_test]
WITH EXECUTE AS OWNER
AS
BEGIN

This does not:

ALTER PROCEDURE [dbo].[truncate_test]

AS
BEGIN

EXECUTE AS OWNER
TRUNCATE TABLE TABLE_1
REVERT

END

So I am trying to avoid running all those many sp's under owner.

I also don't want to create like 40 sp's which are for per table truncate and give exec on them to app and have them change the code which includes that sp instead of truncate statement.

Whats the solution to this, has anyone seen / been thru this before? Is there a sp that I can create which takes the table name as parameter and I can then have the app make the code change in their many sp's and replace the truncate by that sp where they just need to give in the table name, I think I have seen that mentioned on one of the forums but wanted to see if there are no other security side-effects of it and this post is basically to know how DBA's are solving this issue? Thanks a lot.

Answer 1

Don't use sysobjects to look up meta-data, not the least if you work with schemas. This is an old compatibility view for SQL 2000, where schemas worked differently.

The look up on the table name only works if you only have tables in the dbo schema. If you want to handle multiple schemas work with the object id instead. You can retrieve it with the object_id() function.

Answer 2

I have used a method similar to this. https://www.mssqltips.com/sqlservertip/2583/grant-truncate-table-permissions-in-sql-server-without-alter-table/

Create a proc which takes a table name as a parameter, and then use dynamic SQL to truncate the table as the "proxy user". Yes, you will need to update all the TRUNCATE tables, but it is an easy search and replace.

Answer 3

The way to deal with this is to create a certificate and sign the procedure in question with the certificate. You then create a user from that certificate and grant that user ALTER permission on the table(s) in question. This a special type of user that exists to connect permission and certificate. If there are more than one procedure, you typically have one certificate per procedure. This is very easy to handle with some automation.

I describe all of this in a lot more detail, including a stored procedure for automating the procedure, in this article on my web site: http://www.sommarskog.se/grantperm.html.

Answer 4

The complete code is in my post in the old thread. You only need to add CREATE PROCEDURE on top.

The point is that takes the table name and normalises it through the system tables, so it works no matter if it is passed as MyTable, dbo.MyTable, dbo.[MyTable] etc. It want to make it a stored procedure you would pass @Bluemchen as a parameter.

When you sign the procedure, you should grant the certificate user ALTER permission only on the tables you want to permit truncation on, so the SP cannot be abused for something else.

Sorry for the delay in response. I tried last night, but I got an Access Denied which appears to be related to the contents. I had included my code from the old thread, but maybe it works if I do not.

Answer 5

Tom - On our link, there isn't a dynamic sql sp but I do see a reply by Yimy Asprilla with a script on the comments, I also see a similar script here from Yimy here -

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/ed85b96e-dc9c-482d-83ed-45b41eda139d/can-grant-truncate-table-permission-to-a-user?forum=sqldatabaseengine

Erland also replied to the above link which a suggested change on Yimy's script , Erland - would you mind writing the complete code here for the sp, I just want to be sure that I am using the correct code for the sp, as I am no expert on injections, I really appreciate it, thanks.