How to use terraform to auto create synapse sql pool user and grant permission

Question

Hi there,

I would like to ask how to use terraform to auto create synapse sql pool user and grant permission

Thanks&Regards,

zmsoft

Answer 1

Hi @zmsoft ,

Thankyou for using Microsoft Q&A platform and thanks for posting your query here.

As per my understanding you are trying to create Azure Synapse dedicated SQL pool using Terraform . Below are the sample code snippet you can refer to in order to achieve the requirement.

Create a SQL Pool: The SQL Pool in Synapse can be configured in Terraform with the resource name azurerm_synapse_sql_pool. Here is an example usage:

   resource "azurerm_synapse_sql_pool" "default" {
  name                 = "examplesqlpool"
  synapse_workspace_id = azurerm_synapse_workspace.example.id
  sku_name             = "DW200c"
  create_mode          = "Default"
}

Grant Permissions: You can grant access to a single, dedicated SQL pool database by creating a user in the database and granting permissions. Here is an example:

CREATE LOGIN TestAccount WITH PASSWORD = 'QWERTY1234!';
CREATE USER TestAccount FOR LOGIN TestAccount;
EXEC sp_addrolemember 'db_datareader', 'TestAccount';

For more detailed examples and best practices, you can refer to the Shisho Dojo page

Hope it helps. Kindly accept the answer by clicking on Accept answer button. Thankyou