example of how to create a new user in Cloud SQL for SQL Server

create_sql_server_user.py

"""Simple test of connecting to a Cloud SQL for SQL Server instance
and creating a new admin user account.

This script assumes that you've created a Cloud SQL for SQL Server
instance and have the Cloud SQL Proxy running locally and listening
on 127.0.01. For instructions on those setup steps, see
https://github.com/dmahugh/cloud-sql-pyodbc

Note two places below you need to set YOUR-* to your chosen passwords.
"""
import pyodbc

ADMIN_USER = "sqlserver"

# This is the password that you specified for the sqlserver admin account
# when you created your Cloud SQL for SQL Server instance.
ADMIN_PWD = "YOUR-sqlserver-ADMIN-ACCOUNT-PASSWORD"

# In the Cloud Console, I created a database named testdb for testing.
# You can do the same, or change this to a database that exists in your instance.
TEST_DB = "testdb"

# This is the name of the ODBC driver to use. May not be the same on your machine,
# see https://github.com/dmahugh/cloud-sql-pyodbc for more info.
DRIVER = "ODBC Driver 17 for SQL Server"

SERVER = "127.0.0.1"  # Cloud SQL Proxy running on local machine


def main():

    # create a new user account
    username = "NewUser"
    password = "YOUR-PASSWORD-FOR-NEW-USER"
    CONNECTION_STRING = f"DRIVER={{{DRIVER}}};SERVER={SERVER};DATABASE={TEST_DB};UID={ADMIN_USER};PWD={ADMIN_PWD}"
    with pyodbc.connect(CONNECTION_STRING, autocommit=True).cursor() as cursor:
        cursor.execute(
            f"CREATE LOGIN {username} WITH PASSWORD=N'{password}', DEFAULT_DATABASE=[master]"
        )
        cursor.execute(f"USE {TEST_DB}")
        cursor.execute(f"CREATE USER [{username}] FOR LOGIN [{username}]")
        cursor.execute(f"ALTER ROLE [db_ddladmin] ADD MEMBER [{username}]")

    # connect as the new user and create a table
    table = "NewTable"
    CONNECTION_STRING = f"DRIVER={{{DRIVER}}};SERVER={SERVER};DATABASE={TEST_DB};UID={username};PWD={password}"
    with pyodbc.connect(CONNECTION_STRING, autocommit=True).cursor() as cursor:
        cursor.execute(f"DROP TABLE IF EXISTS {table}")
        cursor.execute(f"""CREATE TABLE {table} (column1 CHAR(1))""")

    # print out the tables in the test database
    print(f"Tables in {TEST_DB}:")
    CONNECTION_STRING = f"DRIVER={{{DRIVER}}};SERVER={SERVER};DATABASE={TEST_DB};UID={username};PWD={password}"
    with pyodbc.connect(CONNECTION_STRING).cursor() as cursor:
        cursor.execute("USE testdb")
        for dbname, schema, tablename, *_ in cursor.tables():
            if dbname == TEST_DB and schema == "dbo":
                print(tablename)


if __name__ == "__main__":
    main()