October 14, 2010 at 4:46 am
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..
October 14, 2010 at 4:53 am
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;-)
October 14, 2010 at 5:18 am
Both the users having dbowner permission
October 14, 2010 at 5:50 am
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;-)
October 18, 2010 at 11:17 pm
Yes i have used the following option "be made using this security context".
October 18, 2010 at 11:57 pm
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)
October 19, 2010 at 12:45 am
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?
October 19, 2010 at 3:48 am
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)
October 19, 2010 at 6:11 am
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.
October 19, 2010 at 3:34 pm
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply