Msg 7416, Level 16, State 2, Line 1

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SQL Server 2005 Security » Msg 7416, Level 16, State 2, Line 1

Msg 7416, Level 16, State 2, Line 1

Rate Topic

Display Mode

Topic Options

Author

Message

erikskinner

Posted Tuesday, June 02, 2009 8:55 AM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, January 29, 2013 12:11 PM
Points: 2, Visits: 25

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

landisjanine

Posted Tuesday, March 15, 2011 9:33 AM

Forum Newbie

Group: General Forum Members
Last Login: Friday, April 20, 2012 8:03 AM
Points: 1, Visits: 58

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

Jimmy Soni

Posted Wednesday, January 02, 2013 11:54 AM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, February 05, 2013 12:10 PM
Points: 4, Visits: 178

Was this issue solved???

Post #1402015

« Prev Topic | Next Topic »

Permissions