asked
Why can't I execute as owner through linked server?
I've got a linked server (between two SQL 2008) which use native client (SQLNCLI10) and is set up to use the logins current security context. It works well in all cases, except for a procedure which use EXECUTE AS OWNER. Errormessage (appearing after some time): Msg 65535, Level 16, State 1, Line 0 SQL Server Network Interfaces: Error getting enabled protocols list from registry [xFFFFFFFF] The schema is owned by dbo and the SQL Server Service account is database owner. Service account is a domain account with proper permission on destination server. I've traced on both sides and confirmed - The credential is changed to service account when running proc. - No signs of login attempt at remote side. When I (local admin & sysadmin on both servers) runs the procedure **without** "execute as owner" it works well. My conclution is that the service account lacks some permissions I have. But which? And why? Or is it wrong conclution? Any ideas? /Martin
The Linked Server - what security credentials does it have? See for details on the sp_addlinkedsrvlogin Stored Procedure. Although you may have sysadmin access on the servers individually, if you access a server through the linked server, you access it using different credentials, and this might not have the access you're expecting.
Thanks for quick answer, but it's set up to use the current logins own credentials. EXEC master.dbo.sp_addlinkedserver @server = N'SERVERB', @srvproduct=N'SERVERB', @provider=N'SQLNCLI10', @datasrc=N'SERVERB\ABC' EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'SERVERB',@useself=N'True' /Martin
