how to access sql user in snapshots database

Question

how to access sql user in snapshots database

SQL Galaxy

One Orange Chip

Points: 29472
More actions
June 9, 2020 at 10:00 am

#3761588

Hi all,
Created snapshot database. then this database need to access reporting application So how to access at application side in existing SQL user?

thanks

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

SQL Galaxy One Orange Chip Points: 29472 More actions · Answer 1

Existing Users and permission inherited at snapshots database of Source DB. Try to connect snapshot database existing user in Mirror Instance not connected.

Login failed for user 'Username'. (Microsoft SQL Server, Error: 18456)

Is there any alternative solution for connecting reporting application?

Ant-Green SSC Guru Points: 113445 More actions · Answer 2

Add the user account on both sides of the mirror/AG ensure correct SID mapping etc, re-snap the database.

As you say permissions are inherited from the original database so your user must exist in the original DB in an correct state or grant the account elevated privileges

SQL Galaxy One Orange Chip Points: 29472 More actions · Answer 3

This is existing Database mirror setup.

Followed as per your suggestion.

Created one new user/login at Principal Instance, it is reflected to Snapshots database also in Mirror instance after create Re-Snapshot database.

2. That new login/user not able to add at snapshots database due to read only mode.

Failed to update database "DB_Name_Snap" because the database is read-only. (Microsoft SQL Server, Error: 3906)

Ant-Green SSC Guru Points: 113445 More actions · Answer 4

Is it a SQL authenticated user or a Windows Authenticated user?

If Windows, create the login on Principal and Mirror. Map the user on Principal to the database

If SQL, create the login on Principal, use something like DBATools or sp_help_revlogin to script the user to then be applied to the Mirror. Map the user on Principal to the database.

Once all synced between principal and mirror, drop the snapshots, re-create the snapshot.

SQL Galaxy One Orange Chip Points: 29472 More actions · Answer 5

Thank you Antony.

New SQL User connected to snapshot database in Mirror instance.

Thanks.