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.

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.

  • Thanks for the long answer. I tried to reproduce the situation on different server and the error preventing me is this one: 'The database principal has granted or denied permissions to objects in the database and cannot be dropped.' Is it possible to set up DB so this error won't pop up? – nimdil Dec 4 at 11:50
  • Slight clarification for 3.: DB permissions are granted to Database Users. SQL Server instance permissions are granted to SQL Server Logins (Either SQL Server Native Login w/ password, or a SQL Server Windows Authenticated w/out password). A SQL Server Login can then be linked to a Database User. DB permissions are "transportable". When you backup a DB it contains all the permissions for all Database Users. When you restore the DB to a different instance, then you have to link the Database User to a SQL Server Login (Native or Windows Authenticated). – hot2use Dec 4 at 11:51
  • 1
    @nimdil ah! I understand more now from this comment and your edit of the Q. That is correct, a user shouldn't be able to be dropped if it is the grantor of permission to other users. Its curious that it did in the first instance. – Ian_H Dec 4 at 12:06
  • Well that's at least what appear to be the case because I kicked out the user so I know I encountered no issues. Next day we missed a ton of permissions and the common factor appeared to be the user granting them (dropped user). – nimdil Dec 4 at 13:00
  • 1
    @nimdal Hmmm, I can't seem to replicate it in a lab at all - if a principal is the grantor then the error is thrown, if it isn't then the other permissions stay intact. Do you have a backup from before you dropped the user you could restore somewhere? Just to investigate this. – Ian_H Dec 4 at 13:08

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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