How to Add A User To A Database In Standby / Read-Only Mode In Sql Server

Question

I have a database which is in standby / read-only mode in my reporting server. But, I can not add a new user to this database. How can i add a new user to this database?

Answer 1

Check if the database is part of logshipping. As the database in secondary server will be in Read-only/standby state. ALtering the database directly will not be allowed.

Identify the primary server and add the user there. This will move to your read-only secondary.

Edit : 1

Make sure the logins exist in both the servers.

Answer 2

If it is SQL Login and not Windows Login you might want to create the login (user) on the replica with the same SID, that will assure permissions are replicated as well.

CREATE LOGIN login_name WITH Password = 'strong_password', SID = 0xSome_value

You can get the SID from the primary instance by querying table sys.server_principals.

Select name, sid From sys.server_principals Where name = login_name 

Answer 3

Use bellow commands:

ALTER DATABASE database_name ONLINE

Or try this will also works.

RESTORE DATABASE WITH RECOVERY