How to disable a database user

18 October,2017 by Jack Vamvas

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

 


Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on How to disable a database user


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer