June 2, 2009 at 8:55 am
I have created a linked server using Mgmt Studio (so no scripting to insert).
I have included a "Script Linked Server as" -> Create to
[font="Courier New"]/****** Object: LinkedServer [LinkName] Script Date: 06/02/2009 09:33:28 ******/
EXEC master.dbo.sp_addlinkedserver @server = N'LinkName', @srvproduct=N'any', @provider=N'SQLNCLI', @provstr=N'Server=ServerName;Database=DBName'
/* For security reasons the linked server remote logins password is changed with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'LinkName',@useself=N'False',@locallogin=NULL,@rmtuser=N'RemoteLogin',@rmtpassword='########'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'LinkName',@useself=N'False',@locallogin=N'LocalLogin',@rmtuser=N'RemoteLogin',@rmtpassword='########'
GO
EXEC master.dbo.sp_serveroption @server=N'LinkName', @optname=N'collation compatible', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'LinkName', @optname=N'data access', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'LinkName', @optname=N'dist', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'LinkName', @optname=N'pub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'LinkName', @optname=N'rpc', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'LinkName', @optname=N'rpc out', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'LinkName', @optname=N'sub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'LinkName', @optname=N'connect timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'LinkName', @optname=N'collation name', @optvalue=null
GO
EXEC master.dbo.sp_serveroption @server=N'LinkName', @optname=N'lazy schema validation', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'LinkName', @optname=N'query timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'LinkName', @optname=N'use remote collation', @optvalue=N'true'
GO[/font]
I am using a SQL Login to query specific tables on a remote server. I am not having any issues with the remote SQL Login it is working as expected. When I give the local SQL Login just dbo permission on the local database I recieve the following error:
[font="Courier New"]Msg 7416, Level 16, State 2, Line 1
Access to the remote server is denied because no login-mapping exists.
[/font]
I grant the local login sysadmin and the linked server works. So this begs the question, what permission specific to this local SQL Login do I have to grant without having to grant the login sysadmin?
March 15, 2011 at 9:33 am
I am having the same problem. The local user is a SQL Authenticated user. The account exists on both local and remote server. I am running on SQL2008. I have granted execute rights to xp_prop_oledb_provider in the master catalog. What more is needed?
January 2, 2013 at 11:54 am
Was this issue solved???
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply