update statement doesnt work when using linked server
I have a update statement that is using a linked server method from within a SPROC
when i try and create the SPROC - it fails on first update
showing error message
Msg 7416, Level 16, State 1, Procedure sp_LeaverProcess, Line 7
Access to the remote server is denied because no login-mapping exists.
But when i do
select * from NEMESIS_LINKED.intranet.db
Why is the update failing via the call but the select isnt
when i try and create the SPROC - it fails on first update
showing error message
Msg 7416, Level 16, State 1, Procedure sp_LeaverProcess, Line 7
Access to the remote server is denied because no login-mapping exists.
But when i do
select * from NEMESIS_LINKED.intranet.db
Why is the update failing via the call but the select isnt
CREATE PROCEDURE [dbo].[sp_LeaverProcess]
AS
/* Set user status to Leaver for Intranet */
UPDATE NEMESIS_LINKED.intranet.dbo.tblusers
set status = 2, LeaveDate = GetDate()
FROM NEMESIS_LINKED.Intranet.dbo.tblUsers tblUsers_1
JOIN dbo.view_SelectTodaysLeavers LEAV
ON tblUsers_1.EmployeeID = LEAV.LoginID
/*Revoke Worksite login access */
update ws
set ws.login='N'
from MINOS_LINKED.docs.mhgroup.docusers ws, view_SelectTodaysLeavers TODL
where ws.userid = TODL.LoginID collate Latin1_General_CI_AS
Have you added the necessary login mappings using sp_addlinkedsrvlogin? There's an explanation of linked server security here: http://msdn.microsoft.com/en-us/library/aa213768(SQL.80).aspx
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.