Category: sql server manageability
reb0101 on Mon, 13 Jun 2016 14:33:22
Here I go again….
Let me start, as usual by thanking everyone who has been kind enough to reply to previous posts.
And if you have, or even just read them then you will know I tend to be long winded so I will make an effort to get to the point.
What I have:
Server 1 backups up a database every 12 hours.
Server 2 restores that backup from Server 1.
Server 2 is used for reporting only and is at least 12 hours behind.
When it is restored I have to go to the restored database > Security > Users and delete the user ‘Reporter’.
Then go to Instance Security > Logins and remap user ‘Reporter’ set permissions, etc.
This is where you all say “Always On Availability Groups”!
Well, yea. And that’s where I am.
I have set it up, Server 1 Server 2, same database on both, etc.
The user ‘Reporter’ is on Server 1 (where it pretty much started) and all works well.
But the minute I go into Server to and try and do ANYTHING with user Reporter in either the database level or Instance level I get nowhere QUICK..
When it says “READ ONLY” it means READ ONLY on everything.
I really need to get this one user to be able to hit Server 2 for reporting.
Any ideas and/or quick fixes?
P.S. when I set up group I made it secondary readable
Lydia Zhang on Tue, 14 Jun 2016 03:26:22
Could you please describe more details about that what action you take in the secondary replica with user Reporter? Also please post the full error message you get. As you set the secondary replica as readable, you should be able to query objects in the availability database.
In addition, you should routinely maintain the same set of user logins on every primary database of an Always On availability group and the corresponding secondary databases. However, if you use contained databases, you can configure contained users in the databases, and for these users, you do not need to create logins on the server instances that host a secondary replica.
TechNet Community Support
GuruArthur on Tue, 21 Jun 2016 13:36:24
I think the problem is the SID of the login. On Server1 create the login and grant it all the necessary permissions on your database.
script the login with the SID
create login [reporter] with password ='P@ssword', sid= ...
the sid can be found executing
select name,sid from sys.sql_logins where name = 'reporter'
on server2 drop the user reporter and create it again with the SQL Query created on server1. Additionally you can grant server level permissions to user reporter if you want/need to.