Security for Truncate Table

Question

Post reply

Security for Truncate Table

phant0m3

Valued Member

Points: 56
More actions
November 11, 2003 at 8:52 am

#81524

Is there anyway to allow a user to truncate a table without giving out ddl_admin, system admin, or dbo role access? I tried to do this in a stored procedure but it still gave me a security error.

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

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

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 1

No, not unless the user is the table owner. Is the DELETE operation too intensive?

K. Brian Kelley

http://www.truthsolutions.com/

Author: Start to Finish Guide to SQL Server Performance Monitoring

http://www.netimpress.com/

K. Brian Kelley
@kbriankelley

phant0m3 Valued Member Points: 56 More actions · Answer 2

Yes, the delete operation is to intense. They are deleting millions of rows and this could happen numerous times a day effecting online users response times.

Steve Jones - SSC Editor SSC Guru Points: 736458 More actions · Answer 3

How often does this happen? I've setup a separate table, have the user (through some interface) insert a value here and then have a job run every minute/10 minutes/hour/whatever, and read the table. If there's a value, trunate the table (could even be dynamic) and then mark or delete the row in the table.

Steve Jones

sjones@sqlservercentral.com

http://www.sqlservercentral.com/columnists/sjones

The Best of SQL Server Central.com 2002 - http://www.sqlservercentral.com/bestof/

http://www.dkranch.net

phant0m3 Valued Member Points: 56 More actions · Answer 4

The process is adhoc. The users can start this process any time of the day. I have talked with the developer about the options of a logical delete. Then creating a scheduled job to periodically delete some records through out the day to minimize performance impact for the growth of the tables. They will test this to see if performance is acceptable. The truncate flag does not seem to be an acceptable option in our case.

Thanks for the suggestions. This seems to be a solution to the problem.

Thanks,

Keith Kocik

Jean Maurais Right there with Babe Points: 737 More actions · Answer 5

Another option would be to trigger an alert, which in turn would start a job that truncates the table.

You would need to create a custom non-critical error message that logs to the errorlog and have your user raise that error when he want to empty the table. You then create an alert that responds to this error by starting the job. I have done this before and it works well. The only downside is that you have an entry in your errorlog (and application log) for every instance where this was done.

JM