1

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?

2

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.

  • Thank you very much for the informations. – Furkan Yılmaz Dec 19 '19 at 10:13
  • 1
    If it's not a contained database, I believe you'll need to add the SQL Server logins to the secondary server before you add the users to the primary database. – Sting Dec 19 '19 at 15:30
  • Right.Edited @Sting – Ramakant Dadhichi Dec 19 '19 at 15:46
0

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 
-1

Use bellow commands:

ALTER DATABASE database_name ONLINE

Or try this will also works.

RESTORE DATABASE WITH RECOVERY

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.