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
Last activity: Jan 26, 2023, 08:40 PM