Azure SQL database is not designed to allow users change their passwords. Any workaround?
azure, sql-server
Solution
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.
Problem
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?