Post reply

Server Principal not able to access the database in this security context

  • August 30, 2010 at 4:47 am

    #237484

    Hi

    Would really appreciate if this could be solved. Please see my scenario

    I have a database server(Server01). Also have a user for the server01 named rsa.

    The user was created by DatabaseServer01->Security->Logins.

    UserMapping is done for the database DB1. The database role membership for DB1 for user rsa is db_datareader and public.Once the user is created the same user is also dislayed under DB1->Security->Users.

    I setup Transaction Log Shipping and Server01 is the primary server. I have server 02 which will be my secondary server. i took a full backup of DB1 and when restored is select

    "Leave the database in read only mode(Restore with standby). The restore works. The name of the database in Server02 is DB2(Read Only)

    I have another Custom database, which has a stored proc.This is in server 02. I create the same user rsa which appears in Server01, in Server02 also. I then give execute access to the stored proc for the rsa user.

    The stored procedure will actually collect information from the ReadOnly Database(the user rsa is already there in the Read only database as this is restored from Server01->DB1.).However when i run the stored procedure i get the following error.

    The server principal "rsa" is not able to access the database "DB2" under the current security context.

    THanks

  • August 30, 2010 at 5:11 am

    #1213664

    Hi,

    Did you sync the login rsa on DB2 database on Server2?

    Execute this on DB2 database

    sp_change_users_login Update_one,'rsa', 'rsa'

    Regards,

  • August 30, 2010 at 12:30 pm

    #1213892

    Hi

    Thanks for this one . But it says the following when i try

    Failed to update database DB2 because the database is read-only

    Any other solutions.

  • August 31, 2010 at 2:15 am

    #1214087

    Hi

    Just wanted to let you know that the issue is solved.

    I deleted the rsa user from Server02 and created again with the same sid from Server01.

    create login rsa with password='pass02', sid = sid from server01.

    This worked out and now i am able to execute the stored procedure.

Viewing 4 posts - 1 through 3 (of 3 total)

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