Let's say I have an account at the server level and they have access to database1, database2, and database3 on the server.

If I run ALTER LOGIN "account_name" DISABLE this will disable the account and they can not access any of the databases on the server.

If I want to disable the account from database2, is the only way to do that by DROP USER "account_name"? Is there something similar to the server disable that can be done at the database level?


You can simply deny connect privileges to the specified database. This may be less disruptive than dropping the user (in case you want to give them access again later, you won't have to re-create the user, set up all the permissions again, etc.).

USE database2;
DENY CONNECT TO account_name;

When connect is denied, if they try to establish a new session connected to this database, they will get:

The database database2 is not accessible.

If they try to select from a table in the denied database, for example, they'll see:

Msg 916, Level 14, State 1
The server principal "account_name" is not able to access the database "database2" under the current security context.

To give them access again:

USE database2;
GRANT CONNECT TO account_name;

You can drop the user from the DB at the DB level:

USE [SomeDatabase]

DROP USER [SomeUser]

This will remove the user from the DB, but retain their server level access.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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