23

Users within a database with red X on their icon

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?

28

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:

enter image description here

From the sys.sysusers topic:

enter image description here

1
  • This fixed it for me. But there must be some other underlying issue - my users are created through scripts and have access to multiple databases. Only one user on one DB needed the explicit "CONNECT" permission to be granted. – Morvael Dec 6 '18 at 14:17
6

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.

2
  • Thanks, but we are talking about a RED 'x', not an arrow. – Michael Kirkpatrick Jul 24 '18 at 14:55
  • It's a matter of Studio visualisation, but is the same thing – sepupic Jul 24 '18 at 19:49
0

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.

enter image description here

The user is NOT mapped to the database: enter image description here

If I now create the user:

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

The RED 'x' goes away: enter image description here

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

Here is the info from sysusers: enter image description here

2
  • This is what I was talking about, but you are wrong thinking that "The user is NOT mapped to the database" after you successfully added Win group to a database role.At this same moment corresponded user and schema are created. – sepupic Jul 24 '18 at 20:20
  • 1
    You must have done something other than adding the domain group to a database role, because that doesn't work (unless you are on some ancient version of SQL Server). And also even in this scenario GRANT CONNECT, as my original answer suggested, should have resolved the problem. – Aaron Bertrand Jul 25 '18 at 11:44
0

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 enter image description here

the red mark went away after changing this status.

1
  • 1
    The question is about database-scope Users, not server-scope Logins as your answer mentions. – Oreo Aug 21 '20 at 14:39
-3

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

3
  • Thanks. But they are enabled. – Michael Kirkpatrick Jul 23 '18 at 22:23
  • Users can't be disabled. – Aaron Bertrand Jul 23 '18 at 22:28
  • Does the account exist in Logins folder at the instance level? if it does run the command "Create User [YourLogin] for Login [YourLogin]" w/in the database in question. The Red X should disappear. – Goforebroke Jul 23 '18 at 22:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.