Post reply

Rights necessary to run Begin Transaction and Commit

  • January 6, 2014 at 1:22 am

    #281752

    I have a number of places in my code where I want to make sure that all the data that is being changed is done without errors. So I have:

    Begin Transaction

    -- do a bunch of stuff keeping track if there are any errors

    if anyErrors

    Rollback Transaction

    else

    Commit Transaction

    If I run it as SA, it works perfectly. When the users run it, the Commit hangs the app. If I comment out the Transaction statements, the users can run the code but without any seatbelts.

    Do the users need additional rights in order execute the Commit successfully?

    Thanks.

    __________________________________________________________________________________________________________
    How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • January 6, 2014 at 1:32 am

    #1677666

    From Books Online

    BEGIN TRANSACTION

    Requires membership in the public role.

    COMMIT

    Requires membership in the public role.

    So no additional permissions required.

    To debug, you're going to have to dig into what exactly is happening when the app 'hangs', what waits the queries are seeing, etc.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • January 6, 2014 at 5:14 am

    #1677708

    It may help if we can see all of the sproc. Maybe something else is causing the issue.

    Mark

  • January 6, 2014 at 5:49 am

    #1677726

    GilaMonster (1/6/2014)

    To debug, you're going to have to dig into what exactly is happening when the app 'hangs', what waits the queries are seeing, etc.

    Run a profiler trace and log each separate statement (like: SP:stmtstarting + SP:stmtcompleted and/or SQL:stmtstarting + SQL:stmtcompleted). Filter on the processID and/or loginname to prevent too much data being logged.

    You can also comment out several parts of the code, run it and see if the command completes. If it does complete uncomment parts and try again untill you find the part causing the problem.

    ** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

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

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