Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a sql server user that has the ability to drop any database. I've been running the code below to check the rights that the user has in SQL Server but have not been able to identify how the user has the ability to drop databases. Is there a sql script that can help me identify how this user can drop dbs? Is there a command to deny them dropping any databases? (SSMS is not showing the user as part of the dbcreator role)

3 Answers
3

The query you've got there will list only permissions for the database against which you run it. One way to get permission to drop a database is ALTER ANY DATABASE, which is a server-level permission. To check those, try this query:

This should give you a pretty good idea of the roles that have access to your master database and see weather or not the user has any of those roles. You can also run this against any of your other databases to check the user's permissions on a database by database level. This should be an important tool to help track this down.

Going on documentation here, the security requirements for a user to drop a database are stated as:

Requires the CONTROL permission on the database, or ALTER ANY DATABASE permission, or membership in the db_owner fixed database role

You can explictily deny the permission to one mentioned above but understand that the level you deny it at may not take affect like you think. I recall reading a white paper that walked through how SQL Server goes about validating permissions of a user upon connection but cannot locate it right now. If I recall I may deny them connection to a database but the fact the user is part of the sysadmin role takes precedence.

I would look at auditing specifically for the DROP DATABASE command to be safe.