Using a Stored Procedure to truncate tables?

Question

Post reply

Using a Stored Procedure to truncate tables?

below86

SSChampion

Points: 11532
More actions
June 30, 2014 at 2:01 pm

#308680

I was just looking at an SSIS package someone else set up and I went into one of the execute SQL tasks and it is calling a stored procedure to truncate a table. There are a lot of places that tables are truncated within this SSIS package. But each one the 'database', 'owner', and 'table' name are hard coded.
Like this: exec dbo.uspTruncateTable 'dbname', 'dbo', 'tblname'
Of course there are different names, just changed for an example.
Here is the code in the Stored Procedure.
CREATE procedure [dbo].[uspTruncateTable]
@nDatabase varchar(255),
@nOwner varchar(255),
@ntable varchar(255)
as
declare @sqlString nvarchar(max)
set @sqlString = 'Truncate Table ' + @nDatabase + '.' + @nOwner + '.' + @ntable
exec (@sqlString)
Can anyone explain to me why you would do this? This just seems like an unneeded step, just code the 'truncate table dbname.dbo.tblname'.
Do you think there is some kind of security setting? This just seems really odd to me.
Thanks,
-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 1

Seems like a bit of overkill for such a simple statement.

Maybe they just don't want SQL code inside the SSIS packages.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Matt Crowley SSCarpal Tunnel Points: 4163 More actions · Answer 2

For this procedure, no. Anyone who runs it would need to own the table (or the database), in order for no error to be returned. There does not seem to be any logging, or any other reason why they would need the stored proc to wrap around the dynamic SQL.

You can, of course let a non-dbo/non schema owner truncate a table by using the execute as directive on the stored procedure creation:

create procedure Truncate_TableName

with execute as 'dbo'

as

truncate table tablename

goThis will allow for the truncation of a table (hard coded in this case, but you can get creative) by a regular user (so long as they have execute permissions on the procedure). It also avoids having an extra user able to add/drop columns, indexes, constraints, etc..

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 3

Matt Crowley (6/30/2014)
For this procedure, no. Anyone who runs it would need to own the table (or the database), in order for no error to be returned. There does not seem to be any logging, or any other reason why they would need the stored proc to wrap around the dynamic SQL.
You can, of course let a non-dbo/non schema owner truncate a table by using the execute as directive on the stored procedure creation:
create procedure Truncate_TableName
with execute as 'dbo'
as
truncate table tablename
goThis will allow for the truncation of a table (hard coded in this case, but you can get creative) by a regular user (so long as they have execute permissions on the procedure). It also avoids having an extra user able to add/drop columns, indexes, constraints, etc..

I was thinking the same thing. By using the EXECUTE AS directive, you could avoid giving the execution account of the pacage ddl_admin permissions.

But without it, it just doesn't make sense.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

below86 SSChampion Points: 11532 More actions · Answer 4

Koen Verbeeck (6/30/2014)
Seems like a bit of overkill for such a simple statement.
Maybe they just don't want SQL code inside the SSIS packages.

While I agree with not having SQL in the SSIS package, a truncate table I'm fine with. I't looks like just overkill to me too. I think it was just someone wanting to use stored procedures for everything.:w00t:

-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret.

P Jones SSChampion Points: 12361 More actions · Answer 5

Maybe they have to jump through QA hoops to deploy packages so having the truncate in an sp is done with a view to future amendment or expansion as it is easier to add more statements to a stored proc. than to edit an ssis package and re-deploy it. You could also add statements to disable the non-clustered indexes for example.

But I'd have the SQL Police onto the author for using dynamic sql. in this way.

I've got stored procs that truncate tables but each one does about 40 staging tables, uses TRY...CATCH and does not use dynamic SQL!