locked
Refresh data RRS feed

  • Question

  • I guess this is very common scenario. I have code table inside the database, for example the product table.
    The source data for products is from navision server.

    User should be able to refresh product data from web application, so, the user call procedure, which refresh data.

    If the table is small, the most simple and effective way is to truncate table and transfer again all products from source.
    If the table has many records, than I use change tracking, replication, ....
    Sometimes I also use insert into some third table and use SWAP. 

    The problem is that truncate table permissions are not inherited inside procedure as other permissions are. I don't know why, but that is how it is. So, the user should be in db_owner or db_ddladmin role. But that is not the option.

    Because of that, the DELETE statement is normally used instead of truncate. But delete statement is slow with unnecessary logging. I could do job or assembly or similar, which is executed by system user, but it is ugly solution to do that for every table, which must be refreshed on user request.

    So, I got stuck with DELETE statement. But I would like to know what others prefer in this situations when all data are replaced inside some table?

    Wednesday, February 12, 2014 9:31 AM

Answers

  • You can package TRUNCATE TABLE in a stored procedure without granting the user extra permissions if you sign the procedure with a certificate, create a user from that ceritificate and grant that user ALTER on the table.

    For a detailed description of this method, please see this article on my web site:
    http://www.sommarskog.se/grantperm.html


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Proposed as answer by Satheesh Variath Wednesday, February 12, 2014 10:08 AM
    • Marked as answer by simonxy Wednesday, February 12, 2014 10:31 AM
    Wednesday, February 12, 2014 9:59 AM

All replies

  • You can package TRUNCATE TABLE in a stored procedure without granting the user extra permissions if you sign the procedure with a certificate, create a user from that ceritificate and grant that user ALTER on the table.

    For a detailed description of this method, please see this article on my web site:
    http://www.sommarskog.se/grantperm.html


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Proposed as answer by Satheesh Variath Wednesday, February 12, 2014 10:08 AM
    • Marked as answer by simonxy Wednesday, February 12, 2014 10:31 AM
    Wednesday, February 12, 2014 9:59 AM
  • Erland,

    if I create procedure: WITH EXECUTE AS OWNER
    and grant permission of executing the procedure to application user, truncate works.

    Easy and fast solution :)


    Wednesday, February 12, 2014 10:57 AM
  • But necessarily not the best. If you read my article, you will understand why.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Wednesday, February 12, 2014 11:00 PM