Post reply

sys.dm_tran_commit_table shows 1 day old data with dbo rights & shows all the recent data with sysadmin

  • July 1, 2014 at 2:43 am

    #309611

    While working on a project one of the requirements was using Change Tracking to attain changes for a table. One thing we noticed was the number of committed records is different when running the following query if you have SA access compared to db_owner access. I did some searching in google but really couldn't find a good answer. Does anyone have experience with Change Tracking and why querying a system commit view would give different results based on secuirty level?

    Here is the query that we ran when signed on as SA and then diconnected and reconnected as a SQL ID with db_owner access.

    select * from sys.dm_tran_commit_table order by 1 desc

    Please help

    Regards

    Asir

  • March 2, 2017 at 12:49 pm

    #1931338

    I know this is an old topic, but just came across this issue myself and took a few hours to figure it out.

    There are a few solutions to solve this:

    You may need to run a checkpoint or backup the DB then the dbo user will be able to see the latest changes.
    Give the user VIEW SERVER STATE permission
    Instead of using CHANGE_TRACKING_CURRENT_VERSION() to get the CT_ID which your user might not actually be able to see yet. You can get the max from sys.dm_tran_commit_table. This however will mean that you are potentially behind in moving the data that changed.

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

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