This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(70.4, 13%, 16%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ES%3C/text%3E%3C/svg%3E)
Hi All,
We have recently migrated SQL 2012 EE to SQL 2017. We are on Microsoft SQL Server 2017 (RTM-CU22) 14.0.3356.20 (X64) EE.
Using SSMS, I have logged into SQL 2017 instance as a sysadmin role member and when I go to a specific login -> right click --> Properties -> User Mapping , I see the below message.
Does anyone also faced this issue ? any way to fix this?
And by the way, there are no orphan users in any of the database. Question is why it is showing this message?
We used sp_help_revlogin to script out all logins from old server to new sql server.
SSMS version is below :
Microsoft SQL Server Management Studio 14.0.17277.0
Microsoft Analysis Services Client Tools 14.0.1016.262
Microsoft Data Access Components (MDAC) 10.0.17763.1
Microsoft MSXML 3.0 6.0
Microsoft Internet Explorer 9.11.17763.0
Microsoft .NET Framework 4.0.30319.42000
Operating System 6.3.17763
Regards,
Sam
I'd say it is normal behaviour when the Secondary is not readable.
Just like I mentioned in the comments before.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(73.60000000000001, 21%, 17%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EDB%3C/text%3E%3C/svg%3E)
And adding a user mapping in a secondary database is going to fail anyway, as the non-dbo mappings are stored in each database.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(35.2, 30%, 13%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ETH%3C/text%3E%3C/svg%3E)
Check if you have any databases which are offline or similar.
Hi @Sam ,
Full agree with Dirk, Tibor and Erland. I also check this on my environment as below screenshot. SQL server 2019, SSMS 18.6.
Best regards,
Cathy
If the response is helpful, please click "Accept Answer" and upvote it.
Thanks for repro ing the issue,
Hi Tibor, Its an AG secondary replica. They all are in Syncronized state.
Ah,
I suppose that your secondary is not readable. It is a setting for an AAG if a secondary should be readable or not.
If that's the case (not readable) , then it is normal behaviour.
So none of the databases are outside this AG and are OFFLINE or similar?`Then you have an issue with SSMS, SSMs shouldn't behave like this IMO just because you have secondary AG databases on the instance. I don't have somewhere to test this, but perhaps somebody with non-readable secondaries can test this with a fresh SSMS? It is possible that you have an old SSMS and MS have fixed this.
Hi there,
I've just reproduced it.
I have an dedicated account with sysadmin rights on the secondary.
Switched the secondary so it is not readable.
Got the same "error".
SSMS 18.6 against SQL 2016 SP2 CU14
double check
switched to readable again and no message pops up
Thanks for the confirmation, Dirk. Question now is whether it is worthwhile reporting this to MS... :-) IMO, SSMS should behave nicer in this situation.
Well, good question
I'd say it is not even a bug.
When I connect to the secondary node and all DBs are in one or more AGs which aren't enabled for read intention, then it is normal behaviour.
I'll take a look in my twitter contacts. One is from the SSMS product group.
Then lead him to this QnA. Maybe we'll get further information on this :-)
Samanthar does have an old version of SSMS; I noticed this earlier. He/she has SSMS 17.x, and the most recent version is 18.7.1. But it seems from Dirk's test that it does not really matter.
As for what would be the correct behaviour, I am not sure that I agree with Tibor. After all, the dialogue is not able to fulfil its contract. Whether that is due to the database being OFFLINE or being an unreadable secondary does not matter.
I can appreciate your's and Erland's viewpoint, that this is preferred behavior. But evidently (from this thread) it can cause confusion. IMO, I'd prefer a visual representation in the database list that this database isn't accessible (like it being visually disabled, like greyed). A matter of taste...
Well. not sure if we should raise a bug or so here
https://github.com/microsoft/sqlmanagementobjects/issues
I pinged one twitter contact from the SSMS team. Mabye he has time to take a look on monday.
You're in cc ;-)
IMO, I'd prefer a visual representation in the database list that this database isn't accessible (like it being visually disabled, like greyed).
I can appreciate the sentiment that the message box is is a little intrusive. But I don't think greyed is any good either - that is too discreet. But a yellow warning triangle by the database, tell you that SSMS were not able to display the mapping.
By the way, Dirk, I am sure that SMO is the right place to change this. SMO is only returning the data, and SSMS produces the message box. Then again, SMO they report an exception.
Thanks everyone for the help.