SQL Server – How to Add a User to a Database in Standby/Read-Only Mode

The user has to be granted permissions in the source copy of the database.

The restored copy is just that -- a copy -- of the source, so there's no way to change one and not the other.

Since this is a SQL Server login, you'll need to create the logins on both servers with identical SIDs so the mapping from the database user to the login matches in all cases. You can do this by recreating the login on the secondary, specifying the SID manually in the CREATE LOGIN statement.

If you went with a solution such as replication (not saying you should, just giving an example), this works using two read/write databases, where you could grant permissions completely independently.

Posting this as an answer just because it is too long for a comment, and because it will be relevant to other users pretty soon.

SQL Server 2014 adds some new server-level permissions that will assist with exactly this type of scenario - they were designed with auditing in mind, but this type of requirement seems to fit that bill as well. You could simply add the following two permissions to a server-level login:

CONNECT ANY DATABASE

SELECT ALL USER SECURABLES

The former, like it sounds, allows the login to connect to any database. The nice thing about this is that it will allow this even for databases that are created in the future (provided you don't set explicit deny, which is how you can protect certain user databases from logins that have this permission). The latter allows the login to perform read operations on any database they have access to - so they can SELECT from tables, views, UDFs etc. but they will not be able to perform any UPDATE operations (I haven't tested if this permission understands when a stored procedure performs DML). These work great in combination if you want to give a login wide-open read access to the entire server, or to be more fine-grained you can grant traditional CONNECT privileges to certain databases, and the SELECT ALL USER SECURABLES right will only function for those databases where the login has explicit access to.

The 2014 security changes are documented here - well, partially; they forgot about the database-level permission ALTER ANY DATABASE EVENT SESSION - though that's not relevant here.