0

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

7
  • Did you get any error message?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. Commented Apr 26, 2021 at 23:56
  • Do you know any documentation that can help to achieve this? Please. We have a lot of databases and I would like to automate this process Commented Apr 26, 2021 at 23:58
  • Hi @Nayden Van, I'm afraid no, there isn't a way can auto create the user in Azure SQL database. Like you said , you have many database, we must login every database with the admin account and do it manually. Commented Apr 27, 2021 at 0:40
  • That a bad news. As an alternative approach, can I achieve this by having a pyodbc script were I can pass the server and database name and run a SQL query to add this user right? Commented Apr 27, 2021 at 0:51
  • 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. Commented Apr 27, 2021 at 0:55

1 Answer 1

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.

Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.