Login Fail for user 'XYZ'

[Sasmi_Office]

Dear Friend's, My Issue scenario is as follow's i have two servers Name "Server_A" "Server_B" In "Server_A" i have one Database "DB1" In "Server_B" i have one Database "DB2" in Database "DB1" of Server "Server_A" i have one table "tbl_ListOfCountry" now as per my business logic that table must be centralize where my client will update information and i need to use this table data in "Server_B". For that i have create one procedure which pull all the data of table "tbl_ListOfCountry" from "Server_A". for that i have created Synonym "tbl_ListOfCountry" using following syntex

CREATE SYNONYM [dbo].[tbl_ListOfCountry] FOR [Server_A].[DB1].[dbo].[tbl_ListOfCountry]

now for security purpose i have created one user "user1" with "Server Role" as "Public" and "User Mapping" as "db_datareader", "db_datawriter" and "public" in "Server_B" and Grant As Execute Permission of this user to my procedure "Proc1" which use to pull the record. now the main issue is started whenever i execute that procedure "Proc1" using "user1" login instead of "sa" it give me error Msg 18456, Level 14, State 1, Line 1 Login failed for user 'user1'. Please, help me to overcome this issue... Thanks in advanced..

Sasmi

[Chris Meech]

Let me guess. The password for the 'sa' account on DB1(ServerA) is the same as on DB2(ServerB). :)

Chris Meech I am Canadian. [heard in a local bar] In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]

[Sasmi_Office]

Thanks for the quick reply... I am also think same but when i am using this Procedure in my application it is giving me same error can you explain me why this happening....

[Chris Meech]

I suspect that it works when you are logged in to DB2 as 'sa', because it attempts to use those same credentials on DB1, it will work since 'sa' also exists in DB1 with the same password. When you login to DB2 with 'XYZ', the same login will be attempted on DB1 and since 'XYZ' doesn't exist on DB1, the login fails. The quick and dirty solution is to replicate the userid on DB1 and I suspect that will allow the link to work. :)

Chris Meech I am Canadian. [heard in a local bar] In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]