Create and run a SQL Server migration job containing metadata with an EXECUTE AS clause

Overview

A SQL Server migration job doesn't migrate database user and server login data. Therefore, sources which contain objects defined by users with the EXECUTE AS '[database_user_name]' clause won't be migrated to the new Cloud SQL replica, because the users don't yet exist there. As a result, the overall migration process will fail.

For example:

CREATE OR ALTER PROCEDURE dbo.example
WITH EXECUTE AS 'some_user'
AS BEGIN
…
END

This stored procedure won't be migrated because the some_user user can't be migrated. This is because the user doesn't exist on the target database.

To identify which objects are using the EXECUTE AS '[database_user_name]' clause in your source database, use these queries and check if there are users for them in the destination database.

SELECT s.name AS schema_name, o.name AS object_name, p.name AS user_name
FROM sys.sql_modules m  
INNER JOIN sys.objects o ON o.object_id = m.object_id
INNER JOIN sys.schemas s ON s.schema_id = o.schema_id
INNER JOIN sys.database_principals p ON p.principal_id = m.execute_as_principal_id

To run a migration job from a source which includes such metadata, create the users on your destination Cloud SQL instance before starting your migration job.