SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 SQL Server Administration (2005)
 permission to use linked server
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

heliang
Starting Member

34 Posts

Posted - 04/28/2010 :  11:49:55  Show Profile  Reply with Quote
I have create a linked server with SA account, but when I login with a different user, I am not able to use it. What permission should I grant to this user?

tosscrosby
Aged Yak Warrior

USA
676 Posts

Posted - 04/28/2010 :  12:11:43  Show Profile  Reply with Quote
Does the user exist on the linked server and have appropriate rights to the database(s)? What's the error message you receive?

Terry

-- The problem with socialism is that you eventually run out of other people’s money. -- Margaret Thatcher

Edited by - tosscrosby on 04/28/2010 12:12:17
Go to Top of Page

heliang
Starting Member

34 Posts

Posted - 04/28/2010 :  13:43:18  Show Profile  Reply with Quote

select * from mysqlserver.mydb.test.t_person

Msg 7314, Level 16, State 1, Line 1
The OLE DB provider "SQLNCLI" for linked server "mysqlserver" does not contain the table ""mydb"."test"."t_person"". The table either does not exist or the current user does not have permissions on that table.

not sure how to add user to existing linked server in 2005.
Go to Top of Page

tosscrosby
Aged Yak Warrior

USA
676 Posts

Posted - 04/29/2010 :  08:53:26  Show Profile  Reply with Quote
quote:
Originally posted by heliang


Msg 7314, Level 16, State 1, Line 1
The OLE DB provider "SQLNCLI" for linked server "mysqlserver" does not contain the table ""mydb"."test"."t_person"". The table either does not exist or the current user does not have permissions on that table.

not sure how to add user to existing linked server in 2005.



You don't add it to the linked server definition but the server you are linking to. Once added, assign the appropriate rights for the user (datareader, datawriter, whatever) and give them access to the appropriate database(s) and/or objects.

Also, and I may be wrong here, but I believe you need a four part naming convention such as mysqlserver.mydb.test.t_person, assuming test is the schema owner???

Terry

-- The problem with socialism is that you eventually run out of other people’s money. -- Margaret Thatcher
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000