Monday, January 18, 2016

OPENQUERY - Linked Server error "Deferred prepare could not be completed"

If you're using OPENQUERY and get this error:

OLE DB provider "SQLNCLI10" for linked server "MYREMOTESERVER" returned message "Deferred prepare could not be completed.".
Msg 916, Level 14, State 1, Line 1
The server principal "linkedServer" is not able to access the database "MyDatabase" under the current security context.

Check to be sure the SID of the login is the same as the SID of the database's user:

-- When copying a database from another server, you will need to run this 
-- if the instance already has a login with the same name (because the SIDs 
-- will be different because they came from different instances). 

use MyDatabase

-- Use this to get instance login sid.  
select SUSER_SID('linkedServer')

-- Display a list of users in current database and their User SID. 
exec sp_change_users_login @Action='report'

-- Set database user SID to corresponding instance login SID.  
alter user linkedServer with login = linkedServer


Post a Comment