SQL Server – Disabling an Account at Database Level

sql server

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?

Best Answer

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;
GO
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;
GO
GRANT CONNECT TO account_name;
Related Question