Lesson Learned #312: Cannot drop the login 'xyz', because it does not exist or you do not have

[Guest Jose_Manuel_Jurado]

Today, our customer got the following error message: Answer: Msg 15151, Level 16, State 1, Line 2 - Cannot drop the login 'xzy', because it does not exist or you do not have permission.

 

 

 

We got a special situation with this account because checking the details we saw it was the admin user of Azure SQL Server used at the moment of its creation.

 

 

 

Following the public information placed here: Authorize server and database access using logins and user accounts - Azure SQL Database & SQL Managed Instance & Azure Synapse Analytics | Microsoft Learn there is not possible to change the admin user only reset the password once has been created.

 

 

 

Additionally, we shared with our customer how to obtain the database roles:

 

 

 

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;

 

SELECT roles.principal_id AS RolePrincipalID

, roles.name AS RolePrincipalName

, database_role_members.member_principal_id AS MemberPrincipalID

, members.name AS MemberPrincipalName

FROM sys.database_role_members AS database_role_members

JOIN sys.database_principals AS roles

ON database_role_members.role_principal_id = roles.principal_id

JOIN sys.database_principals AS members

ON database_role_members.member_principal_id = members.principal_id;

 

 

 

 

Related links:

 

 

 

• Database-Level Roles - SQL Server | Microsoft Learn
  
• Server roles - Azure SQL Database | Microsoft Learn
  
• sys.database_role_members (Transact-SQL) - SQL Server | Microsoft Learn
  

 

 

 

Enjoy!

 

Continue reading...