Linked server issue

Question

Post reply

Linked server issue

RockingAdmin

SSCrazy

Points: 2828
More actions
October 14, 2010 at 4:46 am

#145642

Hi,
what permission require for a Local user in the current database and Remote user in the Remote database?
i tried with dbowner permissions to both users (SQL USERS). it was created successfully.But when i try to query the data on Remote database using linked server it giving an error.
Msg 18456, Level 14, State 1, Line 1
Login failed for user 'localuser'.
I can able to access with SSMS,SQLCMD. But i can not able to access using Linked server
What exact permissions are required for a Linked server?
Thanks
Rock..

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 1

try the login (dbowner) available in both sides while setting up linked server

goto properties>>security and select "be made using this security context" and use the login

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

RockingAdmin SSCrazy Points: 2828 More actions · Answer 2

RockingAdmin

SSCrazy

Points: 2828

October 14, 2010 at 5:18 am

#1236732

Both the users having dbowner permission

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 3

rockingadmin (10/14/2010)
Both the users having dbowner permission

have you tried this ?

goto properties>>security and select "be made using this security context" and use the login

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

RockingAdmin SSCrazy Points: 2828 More actions · Answer 4

Yes i have used the following option "be made using this security context".

malleswarareddy_m SSCertifiable Points: 5847 More actions · Answer 5

Hi,

you need to execute two system procedures as defined below.

follow instructions as given below.

USE master

GO

-- To use named parameters:

EXEC sp_addlinkedserver

@server = 'SF-PC035',

@srvproduct = '',

@provider = 'SQLNCLI',

@provstr = 'DRIVER={SQL Server};SERVER=SF-PC035\sqlexpress;userid=sa;password="telemed";'

EXEC sp_addlinkedsrvlogin 'SF-PC035', 'false', NULL, 'sa', 'password'

sp_addlinkedserver is for creating linked server.

sp_addlinkedsrvlogin is for Providing authentication for the linked server.

Malleswarareddy
I.T.Analyst
MCITP(70-451)

RockingAdmin SSCrazy Points: 2828 More actions · Answer 6

creating linked server with SYSDBA user or a SA user it is working fine. my question is like what permission required for a remote user on remote data mapped to local user on local database? and also local user permissions on the local database? will the linked server will work with out SYSDBA or SA or DB_OWNER permissions on the databases?

malleswarareddy_m SSCertifiable Points: 5847 More actions · Answer 7

With out Sys admin permission it will work for remote user.but u have permission for select a table.

Malleswarareddy
I.T.Analyst
MCITP(70-451)

RockingAdmin SSCrazy Points: 2828 More actions · Answer 8

ok. with sysadmin permission it is working fine for me.But i tried with select privilege on a table(Remotedb) to a (Remote)user and also ddl_admin(db role) along with select privilege on a table to a local user on local db, i am got the same error.

@SQLFRNDZ SSCrazy Eights Points: 9095 More actions · Answer 9

give security admin permission it would work on both sides.

[font="Tahoma"]
--SQLFRNDZ[/url]
[/font]