Search code examples
sqlsql-serverazureazure-devopsazure-synapse

Running SQL script on Azure Synapse via Powershell or CLI

I am trying to give a service principal SELECT access on my Azure Synapse SQL data.

CREATE USER [MY_SERVICE_PRINCIPAL] FROM EXTERNAL PROVIDER WITH DEFAFULT_SCHEMA=[dbo]
GO
GRANT SELECT ON DATABASE :: MyDB TO [MY_SERVICE_PRINCIPAL];

This works fine, but it requires me logging into the workspace to do this for every single new service principal. Is it possible to automate this? I automate the creation of the service principal via Azure CLI. Is it possible to run this script from a

Solution

  • The best solution I found was to add a whole Azure AD group as a user on the database manually, then for each new user I'm creating, I automate their addition to the group with some basic Azure CLI commands on a DevOps pipeline rather than try with a SQL Script that adds them individually.