DB users in Database snapshot reporting using database mirroring

Question

Hi,

I created a database snapshot from the mirrored copy for reporting.The snapshot is re-created daily as we require each days data as it is. Now, the problem is with the sql users not being able to access the snapshot. It is the orphan users issue I think. Since the snapshot is readonly, I cant seem to get around this issue.

Any suggestions are appreciated.

Thanks

Prashu

Answer 1

 

 

About Snapshots:

http://msdn.microsoft.com/en-us/library/ms175158.aspx

Limitations and requirements:

http://msdn.microsoft.com/en-us/library/ms189940.aspx

Creating a Database Snapshot:

http://msdn.microsoft.com/en-us/library/ms190677.aspx

Reverting, Dropping:

http://msdn.microsoft.com/en-us/library/ms179557.aspx

How to: View a Database Snapshot (SQL Server Management Studio):

http://msdn.microsoft.com/en-us/library/ms175469.aspx

Other HowTos:

http://msdn.microsoft.com/en-us/library/ms190467.aspx

 

Regards,

Jai

Answer 2

The only way would be to add your users / login in the prod ( principal)server then it gets mirrored to secondary server , so when you are grabbing a snapshot everyday from mirrorred database your reporting user / login will pre-exsit in the db snapshot.

---

Thanks, Leks

Answer 3

Actually ,this is the same issue as orphan users, we cannot resolve this using sp_change_users_login because , the mirror database will be readonly and cannot be updated.

Even though you add the users/login in the production server, the mirror server will only have the user in the db and not the login. If you create a seperate login in the mirrored server, you will still have the orphan user because the SID's will be different.And since its a read-only database you cannot use the sp_change_users_login.

Here is what I have done, I created the associated login on the mirrored server ,for the user using the same SID as that of the user in the mirror DB, this automatically links the user and login as the SID would be same and resolves the issue.

Thanks

Prashu

 

Answer 4

I think i had the same point here , moving your users (add it to primary) / login(manually)  from prod to mirrored instance . It is a known fact that you have to use sp_help_revlogin to move your sql logins (with same sid ) between instances.

---

Thanks, Leks

Answer 5

Prash,

When the db is in restoring \ synchronizing state you are not allowed to do any modifications. So even if you create a database snapshot on the mirrored database it will use the mirrored database underlying SID's. As Lekss mentioned users will be automatically copied to the mirrored server where as logins are not since mirroring is limited database scope. If you want to use the snapshot with SQL logins then you need to drop & recreate (if already exists) or create the logins with the same SID. You can accomplish this with two ways either you can use sp_help_revlogin (said by Lekss) or else you can use Transfer Logins task in SSIS. Some useful links for your reference.

http://support.microsoft.com/kb/918992/ (I think SQL password is also synced)

http://technet.microsoft.com/en-us/library/ms137870.aspx

http://sql-articles.com/articles/dba/transfer-jobs-and-logins-using-ssis

---

Vidhya Sagar. Mark as Answer if it helps!