Azure Sql Server Change Login Password

[Anais Wachowski]

SusanDBAcan login to open a connection to the AnotherDB but cannot execute the Alter Login command to change the password. This reportedly has to be done against the master database. But we don't want the user to connect to the master db for security reasons.

The name of the Server admin account can't be changed after it hasbeen created. To reset the password for the server admin, go to theAzure portal, click SQL Servers, select the server from the list, andthen click Reset Password. To reset the password for the SQL ManagedInstance, go to the Azure portal, click the instance, and click Resetpassword. You can also use PowerShell or the Azure CLI.

Databases in Azure SQL Database, Azure SQL Managed Instance, and Azure Synapse are referred to collectively in the remainder of this article as databases, and the server is referring to the logical server that manages databases for Azure SQL Database and Azure Synapse.

Authentication is the process of proving the user is who they claim to be. A user connects to a database using a user account.When a user attempts to connect to a database, they provide a user account and authentication information. The user is authenticated using one of the following two authentication methods:

With this authentication method, the user submits a user account name and associated password to establish a connection. This password is stored in the master database for user accounts linked to a login or stored in the database containing the user accounts not linked to a login.

Authorization to access data and perform various actions are managed using database roles and explicit permissions. Authorization refers to the permissions assigned to a user, and determines what that user is allowed to do. Authorization is controlled by your user account's database role memberships and object-level permissions. As a best practice, you should grant users the least privileges necessary.

When you first deploy Azure SQL, you can specify a login name and a password for a special type of administrative login, the Server admin. The following configuration of logins and users in the master and user databases occurs during deployment:

The name of the Server admin account can't be changed after it has been created. To reset the password for the server admin, go to the Azure portal, select SQL Servers, select the server from the list, and then select Reset Password. To reset the password for the SQL Managed Instance, go to the Azure portal, select the instance, and select Reset password. You can also use PowerShell or the Azure CLI.

At this point, your server or managed instance is only configured for access using a single SQL login and user account. To create additional logins with full or partial administrative permissions, you have the following options (depending on your deployment mode):

Enable Microsoft Entra authentication and add a Microsoft Entra admin. One Microsoft Entra account can be configured as an administrator of the Azure SQL deployment with full administrative permissions. This account can be either an individual or security group account. A Microsoft Entra admin must be configured if you want to use Microsoft Entra accounts to connect to SQL Database, SQL Managed Instance, or Azure Synapse. For detailed information on enabling Microsoft Entra authentication for all Azure SQL deployment types, see the following articles:

Members of special master database roles for Azure SQL Database have authority to create and manage databases or to create and manage logins. In databases created by a user that is a member of the dbmanager role, the member is mapped to the db_owner fixed database role and can log into and manage that database using the dbo user account. These roles have no explicit permissions outside of the master database.

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.

Create a SQL login in the master database. Then create a user account in each database to which that user needs access and associate the user account with that login. This approach is preferred when the user must access multiple databases and you wish to keep the passwords synchronized. However, this approach has complexities when used with geo-replication as the login must be created on both the primary server and the secondary server(s). For more information, see Configure and manage Azure SQL Database security for geo-restore or failover.

With this approach, the user authentication information is stored in each database, and replicated to geo-replicated databases automatically. However, if the same account exists in multiple databases and you are using SQL authentication, you must keep the passwords synchronized manually. Additionally, if a user has an account in different databases with different passwords, remembering those passwords can become a problem.

To create contained users mapped to Microsoft Entra identities, you must be logged in using a Microsoft Entra account in the database in Azure SQL Database. In SQL Managed Instance, a SQL login with sysadmin permissions can also create a Microsoft Entra login or user.

After creating a user account in a database, either based on a login or as a contained user, you can authorize that user to perform various actions and to access data in a particular database. You can use the following methods to authorize access:

Add the user account to a fixed database role. There are 9 fixed database roles, each with a defined set of permissions. The most common fixed database roles are: db_owner, db_ddladmin, db_datawriter, db_datareader, db_denydatawriter, and db_denydatareader. db_owner is commonly used to grant full permission to only a few users. The other fixed database roles are useful for getting a simple database in development quickly, but are not recommended for most production databases. For example, the db_datareader fixed database role grants read access to every table in the database, which is more than is strictly necessary.

Create a custom database role using the CREATE ROLE statement. A custom role enables you to create your own user-defined database roles and carefully grant each role the least permissions necessary for the business need. You can then add users to the custom role. When a user is a member of multiple roles, they aggregate the permissions of them all.

Grant the user account permissions directly. There are over 100 permissions that can be individually granted or denied in SQL Database. Many of these permissions are nested. For example, the UPDATE permission on a schema includes the UPDATE permission on each table within that schema. As in most permission systems, the denial of a permission overrides a grant. Because of the nested nature and the number of permissions, it can take careful study to design an appropriate permission system to properly protect your database. Start with the list of permissions at Permissions (Database Engine) and review the poster size graphic of the permissions.

When using Microsoft Entra authentication, put Microsoft Entra users into a Microsoft Entra security group. Create a contained database user for the group. Add one or more database users as a member to custom or builtin database roles with the specific permissions appropriate to that group of users.

When using SQL authentication, create contained database users in the database. Place one or more database users into a custom database role with specific permissions appropriate to that group of users.

I just got a message from google that my password for portal.azure.com is compromised. Clicking "change the password" took me to a logon screen where I was able to log in by just clicking my e-mail - no password entered. (Am currently signed in to my outlook mail)

When you generate a SQL Azure server via the SQL Azure portal, you generate a user name and password at the same time. This is your administrative account it has access to all databases on that server. However, you might want to give other people access to some or all of your databases on that server, with full or restricted permissions. This article will show you how to generate additional user accounts on your SQL Azure databases.

Currently, the SQL Azure portal does not allow you to administrate additional users and logins, in order to do this you need to use Transact-SQL. The easiest way to execute Transact -SQL against SQL Azure is to use the SQL Server Management Studio 2008 R2. Learn more about attaching it to SQL Azure here. SQL Server Management Studio 2008 R2 will list the users and logins associated with the databases; however, at this time it does not provide a graphical user interface for creating the users and logins.

You must be connected to the master database on SQL Azure with the administrative login (which you get from the SQL Azure portal) to execute the CREATE LOGIN command. Some of the common SQL Server logins can be used like sa, Admin, root, for a complete list click here.

Users are created per database and are associated with logins. You must be connected to the database in where you want to create the user. In most cases, this is not the master database. Here is some sample Transact-SQL that creates a user:

Just creating the user does not give them permissions to the database. You have to grant them access. In the Transact-SQL example below the readonlyuser is given read only permissions to the database via the db_datareader role.

Fortunately, SQL Server Management Studio 2008 R2 does allow you to delete users and logins. To do this traverse the Object Explorer tree and find the Security node, right click on the user or login and choose Delete.

3a8082e126