The tl;dr answer is that nothing went wrong here and the behaviour is as designed:
DROP USER statement will remove the user (and by extension their permissions).
More full answer:
- SQL Server and T-SQL are powerful tools - there are little to no "are you sure?" checks, such as are common in end user applications, when executing queries - the only things that prevent queries executing are only technological errors (not potential user errors) or any database constraints.
SQL SERVER by default is in autocommit - that is,
IMPLICIT_TRANSACTIONS are off, meaning that the query is committed as soon as it is finished executing - no second chances.
Although there are no options as such, this behaviour can be controlled. As mentioned in Akina's comments, one could add this custom behaviour (for example through triggers). The way that this is controlled out-of-the box is by permissions - only user's with the correct permissions can drop other users - only some of the highest privileges can execute such statements. As per BOL article on
DROP USER, the statement requires the
ALTER ANY USER permission on the database (and of course the
sysadmins can do this)
DROP USER documentation does state the required permissions, although you need to go into more detail to find out why this happens. In SQL Server permissions are granted TO a USER (or LOGIN at the Instance level) or the USER added to a ROLE. The USER has to exist for it to have these privileges.
You can find more about where permissions are stored in SQL Server as per This question by exploring the tables
sys.database_principals - joined on
Princpal_id (and you can see for yourself that without the principal i.e. the USER, their can't be a permissions for it). Nb. Although probably not that risky in most cases, exploring system tables at whim on a production DBs is not advised!
This article explains about permissions in SQL SERVER in detail, and from an authorization point of view This BOL article has more information and is a good start for learning the concepts.
What can you do?
To ensure this doesn't happen again, ensure that only appropriate permissions are given to everyone (Principle of Least Privilege), and those with elevated privileges take care and know what they're doing. Importantly, don't make changes in production with first testing (and testing again)! Also - follow change control procedures appropriate in your company.
To recover, you can either restore from a backup, or you'll have to recreate permissions (ideally you'll have documentation about your system to do this from, if you don't this is the time to make it!).
SQL Server can take no prisoners - and by default SQL Server
COMMITs transactions automatically (and keeping that way is a good thing), be sure of your query before executing and test, test and test again.