Unable to Enable CDC on Azure SQL Database Using Server Admin Login - Error 22902 "Caller is not authorized"

Executed EXEC sys.sp_cdc_enable_db; in master.

That sounds wrong. Why would you enable CDC for master? I think you should run it in the database that you want to enable for CDC.

Hi RM,
It sounds like you’re facing an issue enabling Change Data Capture (CDC) on your Azure SQL Database, getting the error “Caller is not authorized” even while using the server-level admin login and Entra ID service administrator. Here’s a breakdown of what you might want to consider:

Possible Causes and Solutions:

1.Permissions Issue:

• Ensure that the account you’re using to connect actually has the db_owner role on the database. The error you're seeing (Msg 22902) can often be tied back to insufficient privileges.
• You can check the role assignment with the following SQL:

   SELECT is_member('db_owner');

If your user doesn’t show as a member, you can add them:

   EXEC sp_addrolemember 'db_owner', 'your_username';

2. Schema Conflicts:
   • Make sure there is no existing schema or database user named cdc. If such entities exist, they can prevent CDC from being enabled. You may need to drop or rename these entities if they conflict.
3. Service Tier Compatibility:
   • Confirm that your database tier supports CDC. CDC is only enabled on databases that are on the General Purpose tier (or higher). Based on your input, it seems you're already aware of this, but it’s good to double-check.

Steps to Enable CDC:

If you've confirmed the above, here are the steps to try enabling CDC again:

1. Connect to the Database: Make sure you are connected to the appropriate database using a tool like SSMS or Azure Data Studio.
2. Enable CDC for the Database: Run the command again:

   
      EXEC sys.sp_cdc_enable_db;
   
   

3. Verify CDC Status: You can verify if CDC was successfully enabled using this query:

   
      SELECT name, is_cdc_enabled
   
      FROM sys.databases
   
      WHERE name = 'YourDatabaseName';
   
   

Additional Considerations:

• User Trigger Impacts: If you have any user-defined triggers that call functions like SUSER_SNAME() while trying to enable CDC, they can cause failures.
• Check CDC User Existence: Ensure the default cdc user exists and has the db_owner role assigned. If not, create it:

  
     CREATE USER [cdc] WITHOUT LOGIN WITH DEFAULT_SCHEMA = [cdc];
  
     EXEC sp_addrolemember 'db_owner', 'cdc';
  
  

Follow-Up Questions:

1. Can you confirm that the login being used is a member of the db_owner role at the database level?
2. Is there any existing schema or database user named cdc in the database?
3. Have any modifications been made to CDC-related metadata in the past?

References:

• Troubleshooting Change Data Capture Errors
• Enable and Disable Change Data Capture

Hope this helps you get CDC up and running smoothly! If you have any more questions or run into further issues, feel free to ask.

The error message you are encountering (Msg 22902) indicates that the caller is not authorized to enable Change Data Capture (CDC) on the Azure SQL Database. Here are some steps and considerations to address this issue:

1. Database Permissions: Ensure that the user account you are using has the necessary permissions. Even though you are using a server-level admin login, it is important to verify that the login has appropriate permissions on the specific database where you are trying to enable CDC. The db_owner role is required to enable CDC for Azure SQL Database.
2. Check Database Tier: Confirm that your database is on a supported service tier for CDC. CDC is supported for any service tier within the vCore-based purchasing model, and for databases in the DTU purchasing model, it is supported for databases in the S3 tier or higher. If your database is in a lower tier (Basic, S0, S1, S2), you will not be able to enable CDC.
3. Use the Correct Database Context: Make sure you are connected to the specific database (not the master database) when executing the EXEC sys.sp_cdc_enable_db; command. You should execute this command in the context of the database you want to enable CDC for.
4. Check for Existing cdc Schema/User: Ensure that there is no existing schema or user named cdc in the database, as this could prevent CDC from being enabled. If such a schema or user exists, you will need to drop or rename it before enabling CDC.
5. Re-try with Different Accounts: If you have other accounts with db_owner privileges, try using those to see if the issue persists.

If you have verified all of the above and are still encountering issues, consider reaching out to Azure support for further assistance.

References:

• Change data capture (CDC) with Azure SQL Database
• Enable and disable change data capture