How to change password of an Azure SQL Server user(not admin) using azure cli?

Question

I can connect to our org's database through using Azure Active Directory with MFA on SQL Server Management Studio. How to leverage my access through Azure CLI to create and run a script that changes the password of a certain user in our Azure SQL Server, and what minimum access rights/roles must be given to our group to change specific SQL Server users' passwords? Preferably the only will be affected are in a certain list of users, admins and SA are especially excluded.

Answer 1

@Bergs
Thank you for the question and for using Microsoft Q&A platform.

Azure CLI itself does not provide a direct command to update user passwords in Azure SQL Database. Password updates must be performed using SQL statements, and Azure CLI can only facilitate access and manage resources like servers or databases, not execute such granular SQL commands directly.

To update passwords, you need to execute SQL scripts through a tool that can authenticate and run those scripts, such as Azure Data Studio, or SQL Server Management Studio (SSMS).

You can use the below query to alter roles:

ALTER ROLE  role_name  
{  
       ADD MEMBER database_principal  
    |  DROP MEMBER database_principal  
    |  WITH NAME = new_name  
} 

When a SQL Server instance is configured to use Azure Active Directory (AAD) authentication, the management of user accounts and passwords is handled through Azure Active Directory rather than directly through SQL Server. This means that you cannot change the password of an AAD user using SQL queries in SQL Server.

Here are some key points to consider:

1. AAD User Management: Passwords for Azure Active Directory accounts are managed through Azure AD. Users can change their passwords through the Azure portal, or if they are using a work or school account, they may have a self-service password reset option.
2. SQL Server Authentication: If you were using SQL Server authentication (where users have SQL Server-specific usernames and passwords), you could change passwords using T-SQL commands like ALTER LOGIN. However, this does not apply to AAD users.
3. Using Azure Portal: To change the password for an Azure AD user, you would typically go to the Azure portal, navigate to Azure Active Directory, find the user, and then change the password from there.

Minimum Access Rights/Roles Required:

To change the password of a specific SQL Server user, your group needs minimal but sufficient permissions at both the Azure and database levels:

Azure RBAC Roles

SQL DB Contributor role (or equivalent) at the database level. This role provides management rights over the database but does not grant admin access to the server.

SQL Database Roles

At a minimum, you need, ALTER USER permission on the database for the specific users you wish to manage.

If you wish to change the password for a SQL server using SQL authentication then please refer to the following documents:
https://learn.microsoft.com/en-us/sql/t-sql/statements/alter-role-transact-sql?view=azuresqldb-current&preserve-view=true#examples
https://learn.microsoft.com/en-us/azure/azure-sql/database/logins-create-manage?view=azuresql
https://stackoverflow.com/questions/36581188/how-can-a-user-change-their-own-password-for-an-azure-sql-server-database-with-s

Hope this helps. Do let us know if you have any further queries.

---

If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.