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

This post has been republished via RSS; it originally appeared at: Microsoft Tech Community - Latest Blogs - .

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;

Leave a Reply Cancel reply