Sql-server – SQL Server – drop user triggered silent revoke of privileges

permissionssql serversql-server-2016

I encountered following scenario:

A user X granted a ton of permission in the SQL Server database. For example it granted SELECT permission on table Y to user Z.

A user X was dropped with simple DROP USER statement.

No errors encountered. User X disappeared and all permissions he granted with him – including user Z select permission o table Y.

Question 1. Shouldn't there be an error preventing me from dropping user with this kind of implications?
Question 2. Is it controlled by any database option etc.
Question 3. The DROP USER in documentation has no remarks indicating this kind of behaviour – can any elaborate what I'm missing?

Thx

For reference it was SQL SErver 2016 server but database was running in 2008 compatibility mode.

Best Answer

The tl;dr answer is that nothing went wrong here and the behaviour is as designed:

A DROP USER statement will remove the user (and by extension their permissions).

More full answer:

  1. 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.

  1. 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 db_owner and sysadmins can do this)

  2. The 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_permissions and 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!).

Finally

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.

Related Question