How do I delete a user from the master database when the user has been deleted from the Server Logins using the SSMS Object Explorer

All we need is an easy explanation of the problem, so here it is.

I am using an Azure SQL Database. I deleted some users from a Azure SQL database after I saw them mentioned in a Vulnerability Assessment report. Here’s how I deleted them:

  1. Log in to SSMS
  2. Expand the Object Explorer tree
  3. Expand the ‘Security’ folder
  4. Expand the ‘Logins’ folder
  5. Highlight the user
  6. Right-click and select ‘Delete’

I went back to the Vulnerability Assessment blade and ran a new ‘Scan’ but the users I deleted still show up in the list. The list included SQL code to show that my users still exist. I ran that code back in SSMS in the master database and confirmed my users still exist. Here’s the relevant code:

SELECT * FROM sys.database_principals

When I run the following code, I get an error saying the user does not exist (‘or you do not have permission’ – but I am a server admin so I ruled that out):

DROP LOGIN <username>

Note: I already deleted the users from all databases on the server.

How do I get rid of these logins?

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

There’s two concepts with security at play here, and in general how SQL Server works. There are Logins and Users, which the differences are discussed a little further in this StackOverflow answer. You’ve deleted the Logins which is server level, but the Users associated to those Logins still exist at the database level, which has its own Security node you can expand and then a Users node below it.

Whenever you map a Login to a database (to grant database permissions) it creates a User in that database.

Example:

How do I delete a user from the master database when the user has been deleted from the Server Logins using the SSMS Object Explorer

Method 2

SSMS should display this warning when you delete the login:

Deleting server logins does not delete the database users associated
with the logins. To complete the process, delete the users in each
database. It may be necessary to first transfer the ownership of
schemas to new users.

And so in the master database don’t run

DROP LOGIN <username>

. The login has already been dropped. Instead run

DROP USER <username>

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply