One of our database users complains that they can't select from one of the databases in this particualr instance while they have no problems in accessing others in this very same instance. The one that they have access problems is shown as "Standby / Read-Only". The others are just normal.
So, I checked the Security -> Logins for the given principal and looked at the principal's properties to see if there were any user mappings to the read-only database for this login. There were no such mappings for that particular db while there were mappings for other databases and all with "db_datareader" role membership. Initially, I wanted to add a user mapping for this db as well; However, it failed with the below message:
"Failed to update database X because the database is read-only"
Then, I checked the read-only database and found that a user exists in there with the same name as the account that had this issue. It also had "db_datareader" role membership. So, I guess I just need to link the two; however, I don't know how. Please help me! Thanks.
Best Answer
Since the status of the database is Standby, I'm assuming this database is a Log Shipping secondary. If that's the case, the database cannot be written to at all on the secondary (making it writable would break Log Shipping).
Also, since you said that other databases (presumably not read-only) have correct mappings, I'm assuming you're using a SQL login. If that's the case, all you should have to do is recreate the login on the secondary server using
CREATE LOGIN ... WITH SIDto match the SID from the primary (look insys.server_principalson the primary server to find it).On the other hand, if you're using Active Directory for the login, you'll have to add the user principal to the primary database so that when it comes across to the secondary in a log backup, there's no need to do any remapping (note: depending on the situation, this may be a lot easier said than done!).