7

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?

Improve this question
2
  • What are you trying to do exactly? Commented Oct 25, 2016 at 9:41
  • Why do you assume that Azure SQL isn't designed to allow password changes? What type of user is this? Contained users don't have logins, you'd need to use ALTER USER. LOGIN is a server login, which is saved in master. Commented Oct 25, 2016 at 9:57

2 Answers 2

Reset to default
11

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Actually Azure SQL service does support server logins (learn.microsoft.com/en-us/sql/t-sql/statements/…). 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.
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

Improve this answer

1 Comment

This only works for the Azure SQL Server instance administrator account - does not work for any other created logins/users.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.