SQL DBA - How To Enable/Disable Database User in SQL Server

If you see a red down arrow on User name under Users Tab in Database what does that mean? Simple and short answer can be , the user is disable.

Fig 1: User is Disabled in Database

As long as we don't need this user, we are fine to let the user in disable state. But if we have to enable this user, what do we need to do?

If we Deny on Connect permission then the user get disable and by granting Connect permission will enable the user.

Right Click on User and go to properties

Fig 2: Go to User Properties by Right Clicking on User Name

Click on Securables and Add the Database

Fig 3: Connect Permission on Database To User

As we can see in Fig 3, the Connect permission is denied to database user. Remove the check box under Deny and Click under Grant to Enable the Database user.

Fig 4: Enable Database User In SQL Server on Database Level

The user is enabled now. Click on Users Tab and then see if Aamir ( Databsae user) is enabled.

Fig 5: Enable/Disable SQL Server Database User 

If you don't want to do this by using GUI, you can use below script to enable and disable the Database user.

--Enable DataBase User( Provide Connect Permission on Database)
use [YourDataBaseName]
GO
GRANT CONNECT TO [YourDatabaseUserName]
GO


--Disable DataBase User( Deny Connect Permission on Database)
use [YourDataBaseName]
GO
REVOKE CONNECT TO [YourDatabaseUserName]
GO