What does a RED X on a database user mean?

Question

I created two new AD groups and added them as Users of a database, but their icons show with a red X.

What does this mean?

Answer 1

It does not mean the user is disabled (you can only disable logins), it means the user does not have connect privileges to the database. I'm not sure exactly how your users were created, but the easiest way to demonstrate this is:

CREATE LOGIN u1 WITH PASSWORD = 'x', CHECK_POLICY = OFF;
GO
USE tempdb;
GO
CREATE USER u1 FROM LOGIN u1;
GO
ALTER LOGIN u1 DISABLE;
GO
-- u1 has no red x even though the login has been disabled

CREATE USER u2 WITHOUT LOGIN;
GO
-- check Object Explorer, u2 has no red x

DENY CONNECT TO u2;
GO
-- check Object Explorer, u2 now has a red x!

CREATE USER u3 WITHOUT LOGIN;
GO
-- check Object Explorer, u3 has no red x

REVOKE CONNECT FROM u3;
GO
-- check Object Explorer, u3 now has a red x!

(You may need to refresh Object Explorer between GO commands because, well, caching.)

To fix it (assuming you actually want them to be able to connect to the database):

GRANT CONNECT TO [DomainName\BI360Consultants];
GRANT CONNECT TO [DomainName\BI360Users];

Surely you'll need to apply more permissions depending on what you need them to be able to do in the database.

There may be other, more obscure ways to get into this state (e.g. adding a domain group to a role in a database without actually adding a user, as described in MichaelK's answer). Though I'll be honest, when I tried to do what the OP did, the old way or the right way, I was not able to add the domain group to a role without a user present:

-- the old way
EXEC sys.sp_addrolemember N'db_datareader', N'[CAKE\MyGroup]';

Msg 15410, Level 11, State 1, Procedure sp_addrolemember
User or role '[CAKE\MyGroup]' does not exist in this database.

-- the right way
ALTER ROLE db_datareader ADD MEMBER [CAKE\MyGroup];

Msg 15151, Level 16, State 1
Cannot add the principal 'CAKE\MyGroup', because it does not exist or you do not have permission.

Of course with this outcome I did not see any such user in sysusers (deprecated; stop using it) or sys.database_principals. However, if I did this (thanks to sepupic's answer):

GRANT SELECT ON dbo.SomeTable TO [CAKE\MyGroup];

Then the user did show up in those views and did show up as a user in Object Explorer with the red x due to HAS_DBACCESS() = 0. Which still amounts to roughly the same thing: "can't access the database." So if the above GRANT CONNECT does not work (in my case, that did get rid of the red x, but I did not try to actually query the database as that account), also try the following, knowing that it might fail:

CREATE USER [DOMAIN\Group] FROM LOGIN [DOMAIN\Group];

In my case, when I granted connect to this user, it prevented me from running the CREATE USER command:

Msg 15023, Level 16, State 1, Line 16
User, group, or role 'CAKE\MyGroup' already exists in the current database.

This state will always be true for guest/INFORMATION_SCHEMA/sys - with the exception of the guest account on certain system databases. Ignore that and leave them alone.

---

From the sp_addrolemember topic:

From the sys.sysusers topic:

Answer 2

I want only to make an addition to Aaron Bertrand's answer reguarding this one:

It means the user does not have connect privileges to the database (you can't disable users, only logins). I'm not sure exactly how your users were created...

This can happen with Windows principals only in the following way:

Windows login exists at the server level but is not mapped to the database in question, someone decides to grant/deny some permission to this Windows principal at the database level. In this case the corresponding user/schema will be created in database and the row with this grant/deny will be written to sys.database_permissions. This will not give any access to this database because the newly created user still misses the connect permission, and you'll see it in OE with the red arrow.

Answer 3

I think I figured out why it happened.

Scenario:

Domain\BI360Users is an AD group

Domain\BI360Users is added as a login to the server (it has connect permissions)

Domain\BI360Users does NOT exist as a user of a database

I do the following:

USE TEMPDB
GO
EXEC sp_addrolemember N'db_datareader', N'Doamin\BI360users'
GO

Completes successfully.

Refresh: The RED 'x' appears.

The user is NOT mapped to the database:

If I now create the user:

USE TempDB
GO
CREATE USER [Domain\BI360Users] FOR LOGIN [DOMAIN\BI360Users]
GO

The RED 'x' goes away:

So, it appears that there was no user even though the screen show clearly showed it above.

Here is the info from sysusers:

Answer 4

I had the same problem. I fixed it by changing the 'Login' status to 'Enabled' in Status section of the user property in 'Security/Login'section of my SQL server database

the red mark went away after changing this status.

Answer 5

Red X means the logins are disabled w/in SQL Server