How to create login credential similar to the SuperAdmin Credential of SQL server with Least Privilege roles

Devendra Parmar 20 Reputation points
2024-08-06T13:15:32.2466667+00:00

I want to create login credentials that have some permissions similar to super-admin (master credential) in an Azure SQL Database server. following conditions I need:

  1. The credentials have login access to all databases present on the server.
  2. When a new database is created, these credentials should automatically be granted roles that allow them to create users/logins, alter roles, drop users/logins, and grant/revoke access permissions within that database.
  3. It would be helpful to have clarity on the specific permissions or roles that will assigned to these login credential, keeping in mind the principle of least privilege.

Is it possible to achieve this? If so, what server-level roles or permissions do I need to assign to those credentials and how? Please provide guidance.

Azure SQL Database
SQL Server | Other
{count} votes

2 answers

Sort by: Most helpful
  1. MikeyQiaoMSFT-0444 3,300 Reputation points
    2024-08-09T09:34:26.1566667+00:00

    Hi,Devendra Parmar

    1.The credentials have login access to all databases present on the server.

    Yes, but with limitations refered to Authorize database access to SQL Database, SQL Managed Instance, and Azure Synapse Analytics

    You can't create an additional SQL login with full administrative permissions in Azure SQL Database. Only the server admin account or the Microsoft Entra admin account (which can be a Microsoft Entra group) can add or remove other logins to or from server roles. This is specific to Azure SQL Database. 2.When a new database is created, these credentials should automatically be granted roles that allow them to create users/logins, alter roles, drop users/logins, and grant/revoke access permissions within that database.

    The corresponding permissions can be granted at the time of creation. For example, the appropriate security group will have the corresponding permissions.

    3.It would be helpful to have clarity on the specific permissions or roles that will assigned to these login credential, keeping in mind the principle of least privilege.

    Check the following link, which categorizes and introduces the permissions.

    Azure SQL Database server roles for permission management

    Tutorial: Create and utilize Microsoft Entra server loginsBest Regards,

    Mikey Qiao


    If you're satisfied with the answer, don't forget to "Accept it," as this will help others who have similar questions to yours.


  2. Oury Ba-MSFT 20,931 Reputation points Microsoft Employee Moderator
    2024-08-09T17:09:19.61+00:00

    @Devendra Parmar Thank you for reaching out.

    In addition to the above answer.

    You could login in the db as DB Entra Admin and grant the below permissions

    USE master;
    CREATE LOGIN [YourLogin] WITH PASSWORD = 'YourPassword';
    CREATE USER [YourUser] FOR LOGIN [YourLogin];
    
    
    

    Assign Server-Level Roles:

    • dbmanager: Allows the user to create and manage databases.
    • loginmanager: Allows the user to create and manage logins.
    ALTER ROLE dbmanager ADD MEMBER [YourUser];
    ALTER ROLE loginmanager ADD MEMBER [YourUser];
    
    
    

    Grant Database-Level Permissions: For each existing database, you need to create the user and assign the db_owner role:

    USE [YourDatabase];
    

    Azure SQL Database does not support automatic role assignment for new databases. You will need to manually assign the db_owner role to the user for each new database created.

    Hope that clarifies.

    Regards,

    Oury

    0 comments No comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.