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

Apologies for the late reply @Erland Sommarskog , i thought the new site will send a email like the old msdn sql forums on replies, but doesn't look like it, also got busy.

I checked your comment on the old forum and i think you meant a sp like this:

SET ANSI_NULLS ON  
GO  
SET QUOTED_IDENTIFIER ON  
GO  
  
CREATE PROCEDURE [dbo].[usp_Truncate_Table]   
  
@Table_Name varchar(500)  
  
WITH EXECUTE AS OWNER   
AS  
  
DECLARE @sql VARCHAR(500);  
  
SELECT @sql = NULL  
SELECT @sql = 'TRUNCATE TABLE ' + quotename(s.name) + '.' + quotename(o.name)  
FROM   sys.schemas s  
JOIN   sys.objects o ON s.schema_id = o.object_id  
WHERE  o.object_id = object_id(@Table_Name)  
IF @sql IS NULL  
   RAISERROR('No such table %s', 16, 1, @Table_Name)  
PRINT @sql  
EXEC (@sql)  
  
GO  

But i am getting a error while testing,

Msg 50000, Level 16, State 1, Procedure usp_Truncate_Table, Line 21 [Batch Start Line 2]
No such table Table_1

Looks like there is a disconnect somewhere on the join..

Answer 3

Yes @Erland Sommarskog , i did get a email now about your last post, i did change my profile yesterday (had to make all 'instant') - so the emailing works.

Let me look more into the query.. can't seem to figure it out right now, will look again maybe tomorrow, please let me know in case you figure it out before, :), thanks a lot!

Answer 4

I got some time to look into it, below sp works , you need to give the table_name without schema as input, one issue i saw was that if there are 2 tables with the same name in different schema (most env won't have same table names , i would think tho), then it truncates one of the schemas (i did a test between dbo & another schema say 'tst_scm' - it was choosing 'tst_scm' )

CREATE PROCEDURE [dbo].[usp_Truncate_Table_1]   
	  
	@Table_Name varchar(500)  
  
WITH EXECUTE AS OWNER   
AS  
  
DECLARE @sql VARCHAR(500);  
  
SELECT @sql = NULL  
SELECT @sql = 'TRUNCATE TABLE ' + quotename(s.name) + '.' + quotename(o.name)  
from sys.sysobjects o   
join sys.schemas s on o.uid=s.schema_id  
WHERE  o.name = @Table_Name  
IF @sql IS NULL  
   RAISERROR('No such table %s', 16, 1, @Table_Name)  
PRINT @sql  
EXEC (@sql)  
  
  
GO  

To tackle the other issue -> if there are same tablenames in 2 different schema's , Then use the below sp and supply the table_name with schema name this time, maybe there is another way to do it, but this is all i could come up with currently:

CREATE PROCEDURE [dbo].[usp_Truncate_Table_2]   
	  
	@Table_Name varchar(500)  
  
WITH EXECUTE AS OWNER   
AS  
  
DECLARE @sql VARCHAR(500);  
  
SELECT @sql = NULL  
SELECT @sql = 'TRUNCATE TABLE '  + @Table_Name  
  
IF @sql IS NULL  
   RAISERROR('No such table %s', 16, 1, @Table_Name)  
PRINT @sql  
EXEC (@sql)  
  
  
GO  

@Erland Sommarskog - I think sp1 doesn't have sql injection issue, sp2 might have - i am not sure ... what do you think, i would think that sp2 won't be needed in most env's as it is as i don't think you will have same table_names in different schema normally...

Answer 5

Thanks @Erland Sommarskog , i updated the sp:

SET ANSI_NULLS ON  
GO  
  
SET QUOTED_IDENTIFIER ON  
GO  
  
  
  
CREATE PROCEDURE [dbo].[usp_Truncate_Table_3]   
	  
	@Table_Name varchar(500)  
  
WITH EXECUTE AS OWNER   
AS  
  
DECLARE @sql VARCHAR(500);  
  
SELECT @sql = NULL  
SELECT @sql = 'TRUNCATE TABLE ' + quotename(s.name) + '.' + quotename(o.name)  
from sys.objects o   
join sys.schemas s on o.schema_id=s.schema_id  
WHERE  o.name = @Table_Name  
IF @sql IS NULL  
   RAISERROR('No such table %s', 16, 1, @Table_Name)  
PRINT @sql  
EXEC (@sql)  
  
  
GO  

For using object_id() - I looked into it a bit, let me know if you already have the t-sql handy, else i plan to look more into it later on, thanks.