• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 619
  • Last Modified:

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.dbo.tblusers - the select works fine

Why is the update failing via the call but the select isnt

CREATE PROCEDURE [dbo].[sp_LeaverProcess] 
/* 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

Open in new window

1 Solution
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
mooriginalAuthor Commented:
i have
ive fixed this by changing the update statement and aliasing the table
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now