如何用ARM模板创建MSSQL数据库用户？能否授予其所有者权限？

Hey there! Let's tackle your two questions about using ARM templates to manage MSSQL database users clearly and practically.

Creating a MSSQL database user via ARM template relies on the Microsoft.Sql/servers/databases/users resource type. You'll need to reference an existing (or define within the same template) SQL Server and database first.

Here's a concise template example that creates a SQL-authenticated user for a specific database:

{
  "$schema": "https://schema.management.azure.com/schemas/2019-04-01/deploymentTemplate.json#",
  "contentVersion": "1.0.0.0",
  "parameters": {
    "sqlServerName": {
      "type": "string",
      "metadata": { "description": "Name of your existing SQL Server" }
    },
    "databaseName": {
      "type": "string",
      "metadata": { "description": "Name of the target database" }
    },
    "dbUserName": {
      "type": "string",
      "metadata": { "description": "Name of the database user to create" }
    },
    "dbUserPassword": {
      "type": "securestring",
      "metadata": { "description": "Password for the database user" }
    }
  },
  "resources": [
    {
      "type": "Microsoft.Sql/servers/databases/users",
      "apiVersion": "2021-11-01",
      "name": "[concat(parameters('sqlServerName'), '/', parameters('databaseName'), '/', parameters('dbUserName'))]",
      "properties": {
        "authenticationType": "Sql",
        "login": "[parameters('dbUserName')]",
        "password": "[parameters('dbUserPassword')]"
      }
    }
  ]
}

Key notes here:

• The name property follows the format {serverName}/{databaseName}/{userName} to correctly scope the user to the database.
• For SQL authentication, we specify authenticationType: "Sql" along with the login name and password. For Azure AD users, you'd use authenticationType: "ADUser" or "ADGroup" and provide the principalId instead of a password.

Absolutely! You can sequence these operations in an ARM template by using resource dependencies to ensure the user is created before assigning permissions.

To grant the db_owner role, you'll use the Microsoft.Sql/servers/databases/roleAssignments resource type, which depends on the user resource we created earlier. Here's how to extend the previous template to add this step:

{
  // ... (keep the parameters and user resource from above)
  "resources": [
    {
      "type": "Microsoft.Sql/servers/databases/users",
      "apiVersion": "2021-11-01",
      "name": "[concat(parameters('sqlServerName'), '/', parameters('databaseName'), '/', parameters('dbUserName'))]",
      "properties": {
        "authenticationType": "Sql",
        "login": "[parameters('dbUserName')]",
        "password": "[parameters('dbUserPassword')]"
      }
    },
    {
      "type": "Microsoft.Sql/servers/databases/roleAssignments",
      "apiVersion": "2021-11-01",
      "name": "[concat(parameters('sqlServerName'), '/', parameters('databaseName'), '/', 'db_owner/', parameters('dbUserName'))]",
      "dependsOn": [
        "[resourceId('Microsoft.Sql/servers/databases/users', parameters('sqlServerName'), parameters('databaseName'), parameters('dbUserName'))]"
      ],
      "properties": {
        "roleDefinitionName": "db_owner",
        "principalId": "[reference(resourceId('Microsoft.Sql/servers/databases/users', parameters('sqlServerName'), parameters('databaseName'), parameters('dbUserName'))).principalId]"
      }
    }
  ]
}

Important points:

• The dependsOn property ensures the role assignment only runs after the user is successfully created.
• We reference the user's principalId dynamically using the reference() function, which pulls the ID from the newly created user resource.
• The roleDefinitionName is set to db_owner to grant full owner permissions on the database.

内容的提问来源于stack exchange，提问作者Alexey Melezhik