Azure python create user database

Question

I am trying some azure documentation Quickstart tutorial to create a resource group with one SQL Server and one database. The code runs just fine and I am able to create all the resource. Now I was curious how can I run in the same script the code to create a readonly user inside the database I am creating?

This is the code I have:

import os
from azure.common.credentials import ServicePrincipalCredentials
from azure.mgmt.resource import ResourceManagementClient
from azure.mgmt.sql import SqlManagementClient

REGION = 'westus'
GROUP_NAME = 'resource-group-name'
SERVER_NAME = 'server-name'
DATABASE_NAME = 'sample-db'


def run_example():
    subscription_id = os.environ.get(
        'AZURE_SUBSCRIPTION_ID',
        '11111-11-1111-11111-111111')  # your Azure Subscription Id
    credentials = ServicePrincipalCredentials(
        client_id='my-client-id',
        secret='my-secret',
        tenant='tenant'
    )
    resource_client = ResourceManagementClient(credentials, subscription_id)
    sql_client = SqlManagementClient(credentials, subscription_id)

    # You MIGHT need to add SQL as a valid provider for these credentials
    # If so, this operation has to be done only once for each credentials
    resource_client.providers.register('Microsoft.Sql')

    # Create Resource group
    print('Create Resource Group')
    resource_group_params = {'location': 'westus'}
    print_item(resource_client.resource_groups.create_or_update(
        GROUP_NAME, resource_group_params))

    # Create a SQL server
    print('Create a SQL server')
    server = sql_client.servers.create_or_update(
        GROUP_NAME,
        SERVER_NAME,
        {
            'location': REGION,
            'version': '12.0',  # Required for create
            'administrator_login': 'server-login',  # Required for create
            'administrator_login_password': 'pass-word'  # Required for create
        }
    )
    print_item(server)
    print('\n\n')

    # Get SQL server
    print('Get SQL server')
    server = sql_client.servers.get_by_resource_group(
        GROUP_NAME,
        SERVER_NAME,
    )
    print_item(server)
    print("\n\n")

    # List SQL servers by resource group
    print('List SQL servers in a resource group')
    for item in sql_client.servers.list_by_resource_group(GROUP_NAME):
        print_item(item)
    print("\n\n")

    # List SQL servers by subscription
    print('List SQL servers in a subscription')
    for item in sql_client.servers.list():
        print_item(item)
    print("\n\n")

    # List SQL servers usage
    print('List SQL servers usage')
    for item in sql_client.servers.list_usages(GROUP_NAME, SERVER_NAME):
        print_metric(item)
    print("\n\n")

    # Create a database
    print('Create SQL database')
    async_db_create = sql_client.databases.create_or_update(
        GROUP_NAME,
        SERVER_NAME,
        DATABASE_NAME,
        {
            'location': REGION
        }
    )
    # Wait for completion and return created object
    database = async_db_create.result()
    print_item(database)
    print("\n\n")

    # Get SQL database
    print('Get SQL database')
    database = sql_client.databases.get(
        GROUP_NAME,
        SERVER_NAME,
        DATABASE_NAME
    )
    print_item(database)
    print("\n\n")

    # List SQL databases by server
    print('List SQL databases in a server')
    for item in sql_client.databases.list_by_server(GROUP_NAME, SERVER_NAME):
        print_item(item)
    print("\n\n")

    # List SQL database usage
    print('List SQL database usage')
    for item in sql_client.databases.list_usages(GROUP_NAME, SERVER_NAME, DATABASE_NAME):
        print_metric(item)
    print("\n\n")

def print_item(group):
    """Print an Azure object instance."""
    print("\tName: {}".format(group.name))
    print("\tId: {}".format(group.id))
    print("\tLocation: {}".format(group.location))
    if hasattr(group, 'tags'):
        print("\tTags: {}".format(group.tags))
    if hasattr(group, 'properties'):
        print_properties(group.properties)


def print_metric(group):
    """Print an SQL metric."""
    print("\tResource Name: {}".format(group.resource_name))
    print("\tName: {}".format(group.display_name))
    print("\tValue: {}".format(group.current_value))
    print("\tUnit: {}".format(group.unit))


def print_properties(props):
    """Print a ResourceGroup properties instance."""
    if props and props.provisioning_state:
        print("\tProperties:")
        print("\t\tProvisioning State: {}".format(props.provisioning_state))
    print("\n\n")


if __name__ == "__main__":
    run_example()

I am missing this last bit where I want to create this readonly user inside the database I am creating. Thank you very much for your time and help guys

Answer 1

Create user in Azure SQL database is very different with create database instance. It needs the admin account or the enough permission, and the user binds the login, the login must be created in master DB, and the user must be created in current user D, then alter the database role to the user. The code you user is not suitable for create the user.

Even with pyodbc script, you still need the connection string, specify the database/user,/password. The limit is you can't access master DB and user database with one connection string or SQL database connection.

I'm afraid to say we can't do that with the code.