Deny access to one database, allow all the rest

Posted on

Question :

How can I deny access to specific databases from a login? I know that I can walk through and make the login a db_owner on all the ones I want the login to have access to, but how do I do the opposite? I want the login to have access to everything but the ones that I specify

Answer :

I would highly suggest you not assign a login to db_owner just “to have access to” a database unless you want them to have “admin” privileges in that database.

You also have to consider what permissions the login has at the instance level first. Assigning a user to db_denyXXX will have no affect if the login is sysadmin or securityadmin privileges.

If we are talking about a “regular” login on the instance you prevent them from accessing the database by not mapping that login to a user in the database. If no database user exist in the database for the login then they cannot connect to it, as long as they do not have higher privileges at the instance level.

You can also use LOGON TRIGGER to achieve this if you dont want to explicitly map user to db_denydatawriter and db_denydatareader.

This way you just have to change (update or delete) users in a table that is used for logon trigger.

Refer to my script at :

Note: Existing sessions won’t have to go through the trigger. But you can over come that by first killing all the sessions and then enabling the trigger so that all the new incoming connections have to go through the trigger.

You can explicitly map the user to db_denydatareader and db_denydatawriter for that database, either through SSMS or the relevant tsql statements. For paranoia’s sake you could also loop through every object and deny that user select/update/exec rights too.

Leave a Reply

Your email address will not be published. Required fields are marked *