Take the 2-minute tour ×
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.

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

share|improve this question
add comment

3 Answers

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.

share|improve this answer
add comment

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 : http://dba.stackexchange.com/a/37382/8783

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.

share|improve this answer
add comment

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.

share|improve this answer
    
I think you meant db_denydatawriter and db_denydatareader :-) –  Kin Sep 27 '13 at 1:09
    
@Kin: Good catch! Fixed. –  David Spillett Sep 27 '13 at 8:54
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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