How to resolve the users “disabled” issue?

First of all, let’s clarify this up front! There’s no such thing as disabling and enabling users in SQL Server. If you keep on communicating to other professional SQL Server DBAs that you wish to disable or enable a user, we will put you in the “ESPN C’MON MAN!” segment because you had fumbled in the 1 yard line without anyone tackling you. Make no mistake about it. There’s disabling and enabling logins. But, there’s no such thing as disabling and enabling users! What if you see the user has the “Red Down” arrow? Do not Google too much on this topic or else some of the forums will tell you to remove the user and then create it again! Don’t do it. The following will describe the symptom and the remediation.

If you see the user with the “Red Down” arrow like the following screen-shot, the most likely cause is that someone had revoked the “CONNECT” privilege from the user or someone had just performed the following steps: 1) created a login from Windows, 2) created the user in the corresponding database, 3) then added the user to a database role. In both cases, the “CONNECT” privilege is missing.
User CONNECT Permission Revoked

To reproduce this, you can use the following steps:
1. Create the test login.

-- Create Login
USE [master];
CREATE LOGIN [Billy] WITH PASSWORD=N'Bob', 
DEFAULT_DATABASE=[master], 
DEFAULT_LANGUAGE=[us_english], 
CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF; -- this is for demo only

2. Create the test user.

USE [AdventureWorks2012];
CREATE USER [Billy];

3. Add the user to a database role. Note: for SQL Server Logins, you will not experience the “CONNECT” not granted issue as in the case for Windows Logins.

USE [AdventureWorks2012];
EXEC SP_ADDROLEMEMBER 'db_datareader', 'Billy'

4. Revoke the “CONNECT” privilege from the user. Note: you need to use [] around the user as in [Billy]. You will then see the user “disabled” with the “Red Down” arrow next to it.

USE [AdventureWorks2012];
REVOKE CONNECT FROM [Billy];

Solution:
To resolve this issue, you will just need to grant the “CONNECT” privilege to the user. Yes. That’s all she wrote!

USE [AdventureWorks2012];
GRANT CONNECT TO [Billy];

To confirm the user has access:

-- To confirm the user has access to the database
USE [AdventureWorks2012];
SELECT name, 
	hasdbaccess
FROM sys.sysusers 
WHERE name = 'Billy'
Be Sociable, Share!
You can leave a response, or trackback from your own site.

Leave a Reply

You must be logged in to post a comment.