I am trying to set up DMS for a SQL Server RDS to another SQL Server RDS and when I try to run "exec msdb.dbo.rds_cdc_enable_db"

Question

I am trying to run the rds_cdc_enable_db command and I get

The EXECUTE permission was denied on the object rds_cdc_enable_db.

Database is msdb and schema is dbo. I am using the Master login and password for the source RDS.

The SQL Server RDS instance is SQL Server Standard.

score 0 · Answer 1 · answered Jun 25 '19 at 11:20

The rds_cdc_enable_db method requires sysadmin permissions that you don't have on RDS. What you receive as the master login is a custom login that AWS prepared for you and grants you as many permissions as possible without interfering with AWS RDS operations. See docs.

According to RDS documentation, you should rather use stored procedures provided by AWS in order to enable CDC:

--Enable CDC for RDS DB Instance
exec msdb.dbo.rds_cdc_enable_db '<database name>'

--Begin tracking a table
exec sys.sp_cdc_enable_table   
   @source_schema           = N'<source_schema>'
,  @source_name             = N'<source_name>'
,  @role_name               = N'<role name>'

See also this page specifically for DMS.

I am trying to set up DMS for a SQL Server RDS to another SQL Server RDS and when I try to run "exec msdb.dbo.rds_cdc_enable_db"

1 Answers1