Manage permissions on Targets

All the targets belonging to the target group should have the minimum required permissions to run the script under job steps. With Microsoft Entra ID support, the job agent will be able to connect to target databases (databases, servers, elastic pools) and output database(s) using the UMI.

User-assigned managed identity credentials:

In case you have added a user-assigned managed identity on Elastic jobs resource, you need to grant this user-assigned managed identity access to all target databases belonging to the target group.

Image source: Microsoft learn

1. Login to each target Azure SQL server with a Microsoft Entra ID admin account using any client tools like Azure Data Studio or SQL Server Management Studio 18/19.
2. grant minimum permissions to each target database under Azure SQL Server to user-assigned managed identity.

CREATE USER [jobuserUMI] FROM EXTERNAL PROVIDER;

ALTER ROLE db_datareader ADD MEMBER [jobuserUMI];
ALTER ROLE db_datawriter ADD MEMBER [jobuserUMI];

Database scoped credentials

Image source: Microsoft learn

1. Login to each target Azure SQL server using any client tools like Azure Data Studio or SQL Server Management Studio 18/19.
2. Create a login jobuser in the master database. The username should be the same as that of the identity of the job credentials and the password should be the same as the job credentials password.

   --Master db
   CREATE LOGIN [jobuser] WITH PASSWORD '<strong password>';
   

3. Create user jobuser for login jobuser in each target database and grant minimum permissions to the user on the target database to execute job steps.

   --target dbs
   CREATE USER [jobuser] FROM LOGIN jobuser;
   ALTER ROLE db_datareader ADD MEMBER [jobuser];
   ALTER ROLE db_datawriter ADD MEMBER [jobuser];