Post reply

Minimizing role requirements for TRUNCATE TABLE

  • June 12, 2007 at 10:37 am

    #103374

    Forgive me if I am suffering from a skull cramp on this - I may be overlooking something obvious. I have an app that issues TRUNCATE TABLE commands from several SP's (tables used for processing and/or logging, with results preserved between process runs for analysis in the event that there is a problem, but otherwise temporary in nature). We've found that we have to either grant dbo to the app user, or grant the db_ddladmin role to the app user in order to use the TRUNCATE TABLE. Problem as I see it is that these two grants must be at the DB level rather than the table level. We could use a DELETE statement to accomplish most of what the TRUNCATE accomplishes (DELETE doesn't reset the identity columns; TRUNCATE does); however, we implemented the TRUNCATE for significantly better performance.

    What I'd like to find out is if there is a way in SQL 2000 to accomplish something similar to what Oracle does. In Oracle, I can define a SP which truncates a table, and grant the appropriate roles to allow the owner of the db to truncate the table, and give my application user (not the db owner) permission to execute the SP. When my app user executes the SP, it performs the TRUNCATE in the security context of the db owner. Can something similar be done in SQL 2000, or am I making this too complicated?

    Thanks in advance,

    Leighton

  • June 12, 2007 at 7:07 pm

    #712477

    An additional possibilites is to make the owner of the table the database user associated with the application login. See sp_changeobjectowner for how to change the owner of an existing table.

    The application login will now be able to truncate the table. The application login will also be able to alter or drop any existing tables but only those that it owns. To prevent the creation of new objects, use deny for the various statement priveleges.

    Hope this helps.

    The other alternative is to use SQL Server 2005, which has some additional security options for stored procedures including running with the security rights of the sp owner or the rights of the executer.

    SQL = Scarcely Qualifies as a Language

  • June 13, 2007 at 6:50 am

    #712590

    Thanks - I'll give this a shot and see if it will work out in our application. We will be adding support for SQL 2005 in the upcoming months.

Viewing 3 posts - 1 through 2 (of 2 total)

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