this is jut a quick thought (or a reminder) regarding „orphaned users“: There are several scripts out there on how to find an orphaned user account inside a database (I just stumbled across a script a few hours ago, that’s why I’m writing down these words).
Most times DBAs have to deal with an orphaned user when they restore a database to a different server. What then happens is that the SID of the user account won’t match any longer the one from the „old“ server. Therefore you have to “re-map” the database user to a valid SQL Server login. For further information take a look at BOL.
But why am I writing stuff you all already know? Well, there are a few scripts out there which do not only find orphaned users for you but also gives you the chance to delete them. And now my reminder: You have to double check if the database user really isn’t used.
Do you’ve ever heard of a so called „loginless user“? This is a user which only exists inside a database (e.g. there is no corresponding server account). Maybe this user is used as a replacement for an application role? That means a user connects through a application with his credentials to SQL Server, the app then initiates an „Execute as“ and changes the context to our loginless user. If that’s the case then it wouldn’t be the best idea to drop that database user….
So be aware: loginless user != orphaned user