CODEWITHSUNDEEP
sql-serverazure
Mx.Wolf
Mx.Wolf

Reputation: 678

Azure SQL database is not designed to allow users change their passwords. Any workaround?

We start with the facts:

.

--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?

Upvotes: 7

Views: 5605

Answers (2)

Panagiotis Kanavos
Panagiotis Kanavos

Reputation: 131180

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.

Upvotes: 11

KyLim
KyLim

Reputation: 476

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

Upvotes: 1

Related Questions