Azure Question posted in
The official documentation to get you started can be found here.

what role is needed to allow creating new data bases with master database in Azure SQL data base

I have a requirement, where I need to create a new data base in a azure SQL data base by opening a master connection. So for that I need to check if that user have the permission t create new data base or not. For tradition SQL, I am providing ‘dbcreator’ roll to user and

string query = "SELECT IS_SRVROLEMEMBER('dbcreator') IS_DBCREATOR";

using above query to check the if the user have permission to create new data base.

Now same thing I have to implement over azure SQL database. So what roll I have to assign to user/server so I will allow to create new data bases. And what query I need to use to check if user have creator permission.

Tags:

2

Answers


  1. 0

    According the CREATE DATABASE documentation:

    To create a database, the user login must be one of the following
    principals:

    • The server-level principal login
    • The Azure AD administrator for the local Azure SQL Server
    • A login that is a member of the dbmanager database role

    For the dbmanager role membership, the user needs a server level login, a master database user, and membership of the dbmanager database role.

    Example to grant an existing login permissions to create databases in master database context:

    CREATE USER YourDbCreatorUser;
ALTER ROLE dbmanager ADD MEMBER YourDbCreatorUser;

    To check the current user for dbmanager database role membership (in the master database context):

    SELECT IS_MEMBER('dbmanager') IS_DBMANAGER;

    To check if the current user has CREATE DATABASE permissions via dbmanager role membership or other means (e.g. administrator):

    SELECT HAS_PERMS_BY_NAME(NULL, 'DATABASE', 'CREATE DATABASE') AS CanCreateDatabase;
    Login or Signup to reply.
  2. 0

    You can make use of dbmanager and loginmanager roles to grant permission to the user to create a new database. Connect to the master database with server admin and run the below command to add a role to the user:

    ALTER ROLE dbmanager ADD MEMBER <user>;
ALTER ROLE loginmanager ADD MEMBER <user>;

    enter image description here

    Connect to the master database with the user and create database <dbName> to create the table. It will run successfully as shown below:

    enter image description here

    The database will be created successfully as shown below:

    enter image description here

    For more information you can refer this.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search