6

We start with the facts:

  • Logins are in the master database,
  • users are in a user database.
  • Azure does not allow to change database with USE statement.
  • SQL requires the user to be in the master database in order to execute ALTER LOGIN statement.

.

--USE master;
--**ERROR** USE statement is not supported to switch between databases. Use a new connection to connect to a different database.
--GO
ALTER LOGIN nonadmin WITH PASSWORD='new5as$word' OLD_PASSWORD='old5a$sword';
--**ERROR** User must be in the master database. 
GO

It is possible to migrate the database to contained mode, but this way would be quite exhausting as the legacy code have plenty of places like this:

IF(OBJECT_ID('tempdb..#temp1') IS NOT NULL)
BEGIN
  DROP TABLE #temp1
END;

CREATE TABLE #temp1
(
  id int not null IDENTITY,
  CONSTRAINT PK_tt1 PRIMARY KEY(id)
)

Is there a suitable workaround except migrating to contained database mode?

Mx.Wolf
  • 578
  • 5
  • 13

2 Answers2

9

You are trying to change the password of a contained user. Contained users don't have server logins so you can't use the ALTER LOGIN statement.

You need to use ALTER USER :

ALTER USER nonadmin WITH PASSWORD='new5as$word' OLD_PASSWORD='old5a$sword';

A server login is the identity with which you login to a server. In SSMS, you'll find logins in a server's Security node. These logins are then granted access to specific databases as users. These users are stored in the master database.

Panagiotis Kanavos
  • 120,703
  • 13
  • 188
  • 236
  • Actually Azure SQL service does support server logins (https://learn.microsoft.com/en-us/sql/t-sql/statements/create-login-transact-sql?view=azuresqldb-current). You can create a login (when connected to the `master` database and with appropriate privileges) and then create users for this login in any database on the same Azure SQL Server. In this scenario you do have a problem of having to allow connection to `master` to that login, to allow changing the password for the account. – Hilarion Jul 24 '20 at 19:21
1

elect the Database choice on the left, then select Servers: enter image description here

Then, after selecting the server of choice, you'll see the option on the right for resetting admin password: enter image description here

source : Password reset for Azure database

Community
  • 1
  • 1
KyLim
  • 468
  • 1
  • 6
  • 22
  • 1
    This only works for the Azure SQL Server instance administrator account - does not work for any other created logins/users. – Hilarion Sep 02 '20 at 16:46