0

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?

2 Answers 2

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:

Delete Users from Database

5
  • 1
    oh yeah, I forgot to mention that I had already deleted the users from all databases on the server. But I must say your image makes this an excellent answer to that issue.
    – sartoris
    Apr 2, 2021 at 20:36
  • Also, your image shows an on-prem version. I am using an Azure SQL Database (where there is no 'Properties' context menu option).
    – sartoris
    Apr 2, 2021 at 20:42
  • @Sartoris are you certain the user is deleted from all databases? If you're seeing the User in the sys.database_principals system view then likely that User still exists, and specifically in the master database if that's the context where that system view is showing that User record. I'd recommend double checking the Security --> Users node in the master database. Especially since SSMS is using the same source of truth as sys.database_principals. So that would be very weird otherwise.
    – J.D.
    Apr 2, 2021 at 22:14
  • 1
    yes, @David Browne pointed out the user is in the master database as well. Since you added this comment before he replied, I accept your answer.
    – sartoris
    Apr 2, 2021 at 23:04
  • @satoris Great, glad you were able to figure it out! Yup, just to reiterate that when you query sys.database_principals it's only referring to the database context you're currently running it under. In your case, this was the master database.
    – J.D.
    Apr 3, 2021 at 1:42
1

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>
6
  • 1
    You're saying that the the message above refers to the master database as well. That's the part I wasn't getting! Thanks.
    – sartoris
    Apr 2, 2021 at 23:02
  • 1
    @DavidBrowne-Microsoft Hey David, I've always wondered why it doesn't automatically remove the Users too or at least offer the option to do so, when you delete a Login via SSMS, any thoughts why?...just a legacy design that never needed to be upgraded? (I would assume the more common use case is the end user would want to delete all Users associated to that Login too, but maybe not?)
    – J.D.
    Apr 3, 2021 at 1:46
  • @J.D. - I wondered that too, until I actually had to delete an admin type user who had a bunch of roles and permissions that were associated to that user (grantor vs grantee). It appears MS decided that the decision to reassign those objects to another user should be left to the admin (although Microsoft could default the 'orphaned grantors' to dbo). The code to add this feature would not be trivial. I'm sure this was a debate amongst SSMS engineers years ago!
    – sartoris
    Apr 3, 2021 at 20:42
  • Database users can own objects and grant permissions, and there can be an arbitrary number of databases. So cascading a DROP LOGIN to all the mapped users would be tricky. Apr 3, 2021 at 21:04
  • @DavidBrowne-Microsoft Thanks! I would've thought it would be rather simple, especially since SSMS already knows the User Mapping result set, whether it was accomplished iteratively via application code, or dynamically with sp_MSforeachdb or Dynamic SQL. But I guess it does make sense for the developer to take a second thought concerning the owned objects of a user before automatically dropping it. I guess also there are contexts where developers would want users to impersonate (with EXECUTE AS) for permissions reasons but without them being able to login to the server.
    – J.D.
    Apr 3, 2021 at 21:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.