Example database: WarehouseDB
Example user: reporting
Database state: Read only/Standby mode
How do I give the user 'reporting' access to only WarehouseDB without giving them sysadmin privileges?
The problem occurred when I restored a bak file to create WarehouseDB in norecovery mode and then restored another transaction log file to put it in read only/standby mode for doing select queries for reporting purposes. And the reason I do log shipping then onwards is because both production and warehouse database can be synced at all times without any manual intervention. Now if I give this user sysadmin permissions then of course they can query the database but due to security concerns, I can't allow them. Also because it's a read only database, I can't create another user either and map them to WarehouseDB.
Another thing I forgot to mention is, the source database name is for example 'ProductionDB' and when restoring it's changed to WarehouseDB because I have mirroring setup for ProductionDB on the mirrored server where WarehouseDB is then restored.
Any ideas on how to resolve this?