Grant db_read access to my service principle

Question

Hi!

I'm using power automate to automate a task. In order to make this task working I want to give my service principle read permission on the database (I prefer to do this only on a specific table).

However, if I try to give RBAC permissions on sql-server/db, i only get the predescribed roles. Its also not possible to do this with PowerShell scripts.

I guess I need to fix it from DB side, but I dont know where to start. Could someone give me advise?

Thanks!

Answer 1

Hey,

You need to login in within the Azure SQL Database via an AD account that has DB_owner role.

Then execute the below commands :

Create user [<<SPName>>] from External provider

Grant [<<SPname>>] Select on <<TableNm>> --in case if access on specific table

If read access on entire DB :

EXEC sp_addrolemember 'db_datareader', '<<spname>>';

https://azureops.org/articles/grant-access-to-azure-sql-database/#:~:text=Navigate%20to%20Azure%20SQL%20database,will%20select%20the%20admin%20account.