Sample Header Ad - 728x90

What permission have to set for SQL user for configuration of change tracking in Database?

0 votes
1 answer
739 views
I am building application which have to have option to configure (disable/enable) change tracking on tables on specific database. I am connecting to database via SQL user. Which database permissions need to add for SQL user for disable/enable change tracking on tables and option to run below query:
SELECT DISTINCT
       sct1.name                                             AS CT_schema
       , sot1.name                                           AS CT_table
       , ps1.row_count                                       AS CT_rows
       , sct2.name                                           AS tracked_schema
       , sot2.name                                           AS tracked_name
       , CHANGE_TRACKING_MIN_VALID_VERSION(sot2.object_id)   AS min_valid_version
       , itt.create_date                                     AS change_tracking_table_creation_date
       , CAST(ps1.reserved_page_count * 8. / 1024 AS BIGINT) AS CT_reserved_MB
       , CAST(ps2.reserved_page_count * 8. / 1024 AS BIGINT) AS tracked_base_table_MB
       , ps2.row_count                                       AS tracked_rows
FROM sys.internal_tables            it
JOIN sys.objects                    sot1
  ON it.object_id = sot1.object_id
JOIN sys.schemas                    AS sct1
  ON sot1.schema_id = sct1.schema_id
JOIN sys.dm_db_partition_stats      ps1
  ON it.object_id = ps1.object_id
     AND ps1.index_id IN (0, 1)
LEFT JOIN sys.objects               sot2
       ON it.parent_object_id = sot2.object_id
LEFT JOIN sys.schemas               AS sct2
       ON sot2.schema_id = sct2.schema_id
LEFT JOIN sys.dm_db_partition_stats ps2
       ON sot2.object_id = ps2.object_id
          AND ps2.index_id IN (0, 1)
INNER JOIN sys.internal_tables      itt
        ON itt.name = sot1.name
WHERE it.internal_type IN (209, 210);
Asked by adam.g (465 rep)
Jan 25, 2023, 02:11 PM
Last activity: Jan 26, 2023, 08:40 PM