how to access sql user in snapshots database

  • 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

     

  • 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?

  • 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

  • This is existing Database mirror setup.

    Followed as per your suggestion.

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

  • 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.

  • Thank you Antony.

    New SQL User connected to snapshot database in Mirror instance.

    Thanks.

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

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