what kind of permissions do I need to grant to database user?

Hi @Huang, Winston-HR ,

Thank you for using Microsoft Q&A platform and thanks for your question.

As I understand you're looking for the kind of permissions which is required for database user. If my understanding is incorrect, please let me know.

Azure Synapse Analytics uses role-based access control (RBAC) to manage permissions and access to resources.

To back up tables from schema A to schema B and implement them through stored procedures, you can grant the 'select' permission of schema A to userA, and grant the 'select, update, insert, delete' permissions of schema B to userA. This will allow userA to select data from schema A and perform all CRUD operations on schema B. This should be sufficient for your requirements.

To authenticate users through userA, you can create stored procedures in schema B that use userA's credentials to access schema A. You can then grant execute permissions on these stored procedures to the users who need to access the data.

Here are the steps to grant the required permissions to userA:

• Connect to the database where schema A and schema B are located.
• Execute the following command to grant 'select' permission of schema A to userA:

   GRANT SELECT ON schemaA TO userA;
   

• Execute the following command to grant 'select, update, insert, delete' permissions of schema B to userA:

   GRANT SELECT, UPDATE, INSERT, DELETE ON schemaB TO userA;
   

• Create stored procedures in schema B that use userA's credentials to access schema A.
• Grant execute permissions on these stored procedures to the users who need to access the data.

Reference:

• granting permissions in Azure SQL Database
• Grant Permissions on a Stored Procedure

Hope this helps. Do let us know if you any further queries.

If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.