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?
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.Sign up to join this community
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
sys - with the exception of the guest account on certain system databases. Ignore that and leave them alone.
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
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
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.
I think I figured out why it happened.
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
Refresh: The RED 'x' appears.
If I now create the user:
USE TempDB GO CREATE USER [Domain\BI360Users] FOR LOGIN [DOMAIN\BI360Users] GO
So, it appears that there was no user even though the screen show clearly showed it above.