How to disable a database user

Question: Is it possible to disable a user on a database or do they have to be removed totally?

Answer: You don't need to remove a database user totally to disable them. A database user is associated with a SQL Server Login. So to disable a database user would requires a step to disable the login.

To disable Logins of a SQL Server:

Use master
Go
Alter Login loginname disable;


--To enable Logins of a server:
   Use master
   Go
 Alter Login loginname enable;

You may notice that next to some database users a downward pointing red arrow. This means the database user does not have access to the database. You confirm by executing the query : select * from sys.sysusers where name = ‘xxxxx’. Check the “hasdbaccess” column.

This gives you another option of how to deny a database user access to the database. You can use the REVOKE CONNECT command.

The method you use to manage database users and database access will partly depend on the circumstamces and also other organisation policies  such as SQL Server Security Policy

USE MYDB
GO
REVOKE CONNECT FROM User1



USE MYDB
GO
GRANT CONNECT TO User1
 

USE MYDB
SELECT name, hasdbaccess FROM sys.sysusers WHERE name = 'User1'
Keywords:grant user access, grant user access to database

Read More on database user management

Restricted user on database after restore (SQL Server DBA)

Script to change database owner to sa for all user databases (SQL ...

SQL Server Security Policy