Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Msg 7416, Level 16, State 2, Line 1 Expand / Collapse
Author
Message
Posted Tuesday, June 02, 2009 8:55 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, April 08, 2014 1:48 PM
Points: 2, Visits: 38
I have created a linked server using Mgmt Studio (so no scripting to insert).

I have included a "Script Linked Server as" -> Create to


/****** 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



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:

Msg 7416, Level 16, State 2, Line 1
Access to the remote server is denied because no login-mapping exists.


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?
Post #727484
Posted Tuesday, March 15, 2011 9:33 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, April 08, 2014 10:52 AM
Points: 1, Visits: 64
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?
Post #1078436
Posted Wednesday, January 02, 2013 11:54 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, March 11, 2014 4:56 PM
Points: 4, Visits: 181
Was this issue solved???
Post #1402015
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse