After Azure SQL Login Password Change – Error 916

My organization requires periodic password changes on all its accounts.  After changing a password for a local login for a Azure SQL Instance, this account was able to login into the Azure SQL Instance, but not connect to its target database.  I received the “Microsoft SQL Server, Error: 916” when I bought the database in to focus.

PROBLEM:

Using SQL Server Management Studio (SSMS), I changed the login’s password in the master database using the following SQL:

-- Using the Master Database 
ALTER LOGIN AzureSQLuser01 WITH PASSWORD = 'CrAzyL0ng#But#SafePassW0rd' ; 
GO

Again using SSMS to connect with the altered login, I was able to connected Azure SQL Instance.  I did see a list of all the databases in the Azure SQL Instance.  When I clicked on the database that I had permission, I received the following error:

Contents of the copied error message:

TITLE: Connect to Database Engine
——————————
Cannot connect to my-azure-server7603.database.windows.net.
——————————
ADDITIONAL INFORMATION:
The server principal “AzureSQLuser01” is not able to access the database “MyAzureDB” under the current security context.
Cannot open database “MyAzureDB” requested by the login. The login failed.
Login failed for user ‘AzureSQLuser01’. (Microsoft SQL Server, Error: 916)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&EvtSrc=MSSQLServer&EvtID=916&LinkId=20476

Resolution:

Short answer, I had to drop & recreate the user in the problem database.

• Need to find the roles that this login has as a user in this database:

--
-- Finding the roles of the current user
--
-- I am not the author of this query - just found it on google
--
SELECT 
DP1.name AS DatabaseRoleName,
isnull (DP2.name, 'No members') AS DatabaseUserName
FROM sys.database_role_members AS DRM
RIGHT OUTER JOIN sys.database_principals AS DP1
ON DRM.role_principal_id = DP1.principal_id
LEFT OUTER JOIN sys.database_principals AS DP2
ON DRM.member_principal_id = DP2.principal_id
WHERE DP1.type = 'R'
ORDER BY DP1.name;

• Need to drop & recreate the user in the problem database

--
-- Need to drop & recreate the user in the problem database
--
DROP USER [AzureSQLuser01];
GO

CREATE USER [AzureSQLuser01] FOR LOGIN [AzureSQLuser01] WITH DEFAULT_SCHEMA=[dbo]
GO

ALTER ROLE db_datawriter ADD MEMBER AzureSQLuser01;
GO

ALTER ROLE db_datareader ADD MEMBER AzureSQLuser01;
GO

Sidenote:

I am not happy with this solution. However, my search of the internet has not found a better answer. This does not happen with all my password changes. Unable to find a root cause on this issue.

If someone has found a better solution then this, or the root cause, please leave it in the comment below, and I will happily update this post.