I am trying to create an SQL Server user that I will use to run linked
servers.
How do I create such account? What rights should I give to it?

Thanks

Mark,

I really do not quite understand your questions, since there are several
ways to access a linked server. As yourself, as a specific account, mapping
accounts between servers, etc. The commands you might use are:
sp_addlinkedserver
sp_serveroption
sp_addlinkedsrvlogin

These are also settable through the desktop tools, such as SQL Server 2000
Enterprise Manager or SQL Server 2005 Management Studio. Although the
position in the object tree is different between the two, the functionality
it pretty much the same.

If that does not , perhaps you could clarify:
1 - "run linked servers" - Do you mean access them through the link? Do you
mean what account the other server should be running under?
2 - "create such account" - See above for commands.
3 - "what rights" - It depends on the rights your functions need and (see
above) how you choose to connect. If you connect through the linked server
under you own credentials, no other accounts are needed, but your account
will need some rights.

You did not mention what version you are running, but the Books Online for
your version will discuss linked servers and their security. You might look
for topics like:
Linking Servers
Security for Linked Servers
Configuring Linked Servers for Delegation

RLF

"Mark Goldin" <mgoldin@ufandd.com> wrote in message
news:%23ZKplNzDIHA.3848@TK2MSFTNGP05.phx.gbl...
>I am trying to create an SQL Server user that I will use to run linked
>servers.
> How do I create such account? What rights should I give to it?
>
> Thanks
>

<your account will need some rights.
What are these?
<Do you mean access them through the link?
I am running the following:
select * from linkservername...remotetable


"Russell Fields" <russellfields@nomail.com> wrote in message
news:%23agDAWOEIHA.5208@TK2MSFTNGP04.phx.gbl...
> Mark,
>
> I really do not quite understand your questions, since there are several
> ways to access a linked server. As yourself, as a specific account,
> mapping accounts between servers, etc. The commands you might use are:
> sp_addlinkedserver
> sp_serveroption
> sp_addlinkedsrvlogin
>
> These are also settable through the desktop tools, such as SQL Server 2000
> Enterprise Manager or SQL Server 2005 Management Studio. Although the
> position in the object tree is different between the two, the
> functionality it pretty much the same.
>
> If that does not , perhaps you could clarify:
> 1 - "run linked servers" - Do you mean access them through the link? Do
> you mean what account the other server should be running under?
> 2 - "create such account" - See above for commands.
> 3 - "what rights" - It depends on the rights your functions need and (see
> above) how you choose to connect. If you connect through the linked
> server under you own credentials, no other accounts are needed, but your
> account will need some rights.
>
> You did not mention what version you are running, but the Books Online for
> your version will discuss linked servers and their security. You might
> look for topics like:
> Linking Servers
> Security for Linked Servers
> Configuring Linked Servers for Delegation
>
> RLF
>
> "Mark Goldin" <mgoldin@ufandd.com> wrote in message
> news:%23ZKplNzDIHA.3848@TK2MSFTNGP05.phx.gbl...
>>I am trying to create an SQL Server user that I will use to run linked
>>servers.
>> How do I create such account? What rights should I give to it?
>>
>> Thanks
>>
>
>

Mark,

Origin Machine -- The server you issue your query from
Linked Server -- The definition on the Origin Server granting an access path
the the Target Machine
Target Machine -- The server to be accessed from the above linked server.

A user account needs no special rights to use a linked server defined on the
origin machine. But he does need to have the appropriate rights granted on
the target machine. The rights on the target machine are not special to the
linked server access either; they simply are the same rights that someone
logged on directly to the target machine would need for the functionality to
work.

However you defined access to be passed through the linked server (you did
not mention how you did that) the account that accesses the target server
needs rights to the objects it is to access. If they authenticate as
themselves, then their login needs rights. If the linked server is defined
to force access to a specific account, that account needs the rights.

You can secure the linked server to a limited list of people by defining
each login that should be able to use the linked server and then setting the
linked security definition to deny rights to anybody not on your list. As I
understand it, the list must be logins not groups.

FWIW,
RLF

"Mark Goldin" <mgoldin@ufandd.com> wrote in message
news:%231eMx%23XEIHA.3712@TK2MSFTNGP02.phx.gbl...
> <your account will need some rights.
> What are these?
> <Do you mean access them through the link?
> I am running the following:
> select * from linkservername...remotetable
>
>
> "Russell Fields" <russellfields@nomail.com> wrote in message
> news:%23agDAWOEIHA.5208@TK2MSFTNGP04.phx.gbl...
>> Mark,
>>
>> I really do not quite understand your questions, since there are several
>> ways to access a linked server. As yourself, as a specific account,
>> mapping accounts between servers, etc. The commands you might use are:
>> sp_addlinkedserver
>> sp_serveroption
>> sp_addlinkedsrvlogin
>>
>> These are also settable through the desktop tools, such as SQL Server
>> 2000 Enterprise Manager or SQL Server 2005 Management Studio. Although
>> the position in the object tree is different between the two, the
>> functionality it pretty much the same.
>>
>> If that does not , perhaps you could clarify:
>> 1 - "run linked servers" - Do you mean access them through the link? Do
>> you mean what account the other server should be running under?
>> 2 - "create such account" - See above for commands.
>> 3 - "what rights" - It depends on the rights your functions need and (see
>> above) how you choose to connect. If you connect through the linked
>> server under you own credentials, no other accounts are needed, but your
>> account will need some rights.
>>
>> You did not mention what version you are running, but the Books Online
>> for your version will discuss linked servers and their security. You
>> might look for topics like:
>> Linking Servers
>> Security for Linked Servers
>> Configuring Linked Servers for Delegation
>>
>> RLF
>>
>> "Mark Goldin" <mgoldin@ufandd.com> wrote in message
>> news:%23ZKplNzDIHA.3848@TK2MSFTNGP05.phx.gbl...
>>>I am trying to create an SQL Server user that I will use to run linked
>>>servers.
>>> How do I create such account? What rights should I give to it?
>>>
>>> Thanks
>>>
>>
>>
>
>

I have got it working setting up SQL Server box as a trusted object for
deligation.
But it only works for me since I am a local admin of that box.
What do I need to do to have a regular user be able to run LS?

Thanks

"Russell Fields" <russellfields@nomail.com> wrote in message
news:uUbNEqZEIHA.3916@TK2MSFTNGP02.phx.gbl...
> Mark,
>
> Origin Machine -- The server you issue your query from
> Linked Server -- The definition on the Origin Server granting an access
> path the the Target Machine
> Target Machine -- The server to be accessed from the above linked server.
>
> A user account needs no special rights to use a linked server defined on
> the origin machine. But he does need to have the appropriate rights
> granted on the target machine. The rights on the target machine are not
> special to the linked server access either; they simply are the same
> rights that someone logged on directly to the target machine would need
> for the functionality to work.
>
> However you defined access to be passed through the linked server (you did
> not mention how you did that) the account that accesses the target server
> needs rights to the objects it is to access. If they authenticate as
> themselves, then their login needs rights. If the linked server is
> defined to force access to a specific account, that account needs the
> rights.
>
> You can secure the linked server to a limited list of people by defining
> each login that should be able to use the linked server and then setting
> the linked security definition to deny rights to anybody not on your list.
> As I understand it, the list must be logins not groups.
>
> FWIW,
> RLF
>
> "Mark Goldin" <mgoldin@ufandd.com> wrote in message
> news:%231eMx%23XEIHA.3712@TK2MSFTNGP02.phx.gbl...
>> <your account will need some rights.
>> What are these?
>> <Do you mean access them through the link?
>> I am running the following:
>> select * from linkservername...remotetable
>>
>>
>> "Russell Fields" <russellfields@nomail.com> wrote in message
>> news:%23agDAWOEIHA.5208@TK2MSFTNGP04.phx.gbl...
>>> Mark,
>>>
>>> I really do not quite understand your questions, since there are several
>>> ways to access a linked server. As yourself, as a specific account,
>>> mapping accounts between servers, etc. The commands you might use are:
>>> sp_addlinkedserver
>>> sp_serveroption
>>> sp_addlinkedsrvlogin
>>>
>>> These are also settable through the desktop tools, such as SQL Server
>>> 2000 Enterprise Manager or SQL Server 2005 Management Studio. Although
>>> the position in the object tree is different between the two, the
>>> functionality it pretty much the same.
>>>
>>> If that does not , perhaps you could clarify:
>>> 1 - "run linked servers" - Do you mean access them through the link? Do
>>> you mean what account the other server should be running under?
>>> 2 - "create such account" - See above for commands.
>>> 3 - "what rights" - It depends on the rights your functions need and
>>> (see above) how you choose to connect. If you connect through the
>>> linked server under you own credentials, no other accounts are needed,
>>> but your account will need some rights.
>>>
>>> You did not mention what version you are running, but the Books Online
>>> for your version will discuss linked servers and their security. You
>>> might look for topics like:
>>> Linking Servers
>>> Security for Linked Servers
>>> Configuring Linked Servers for Delegation
>>>
>>> RLF
>>>
>>> "Mark Goldin" <mgoldin@ufandd.com> wrote in message
>>> news:%23ZKplNzDIHA.3848@TK2MSFTNGP05.phx.gbl...
>>>>I am trying to create an SQL Server user that I will use to run linked
>>>>servers.
>>>> How do I create such account? What rights should I give to it?
>>>>
>>>> Thanks
>>>>
>>>
>>>
>>
>>
>
>

BTW, this is a message I am getting when I am trying to run query using LS
when I am not a local admin:
Cannot create an instance of OLE DB provider "VFPOLEDB" for linked server
"specifications".

"Russell Fields" <russellfields@nomail.com> wrote in message
news:uUbNEqZEIHA.3916@TK2MSFTNGP02.phx.gbl...
> Mark,
>
> Origin Machine -- The server you issue your query from
> Linked Server -- The definition on the Origin Server granting an access
> path the the Target Machine
> Target Machine -- The server to be accessed from the above linked server.
>
> A user account needs no special rights to use a linked server defined on
> the origin machine. But he does need to have the appropriate rights
> granted on the target machine. The rights on the target machine are not
> special to the linked server access either; they simply are the same
> rights that someone logged on directly to the target machine would need
> for the functionality to work.
>
> However you defined access to be passed through the linked server (you did
> not mention how you did that) the account that accesses the target server
> needs rights to the objects it is to access. If they authenticate as
> themselves, then their login needs rights. If the linked server is
> defined to force access to a specific account, that account needs the
> rights.
>
> You can secure the linked server to a limited list of people by defining
> each login that should be able to use the linked server and then setting
> the linked security definition to deny rights to anybody not on your list.
> As I understand it, the list must be logins not groups.
>
> FWIW,
> RLF
>
> "Mark Goldin" <mgoldin@ufandd.com> wrote in message
> news:%231eMx%23XEIHA.3712@TK2MSFTNGP02.phx.gbl...
>> <your account will need some rights.
>> What are these?
>> <Do you mean access them through the link?
>> I am running the following:
>> select * from linkservername...remotetable
>>
>>
>> "Russell Fields" <russellfields@nomail.com> wrote in message
>> news:%23agDAWOEIHA.5208@TK2MSFTNGP04.phx.gbl...
>>> Mark,
>>>
>>> I really do not quite understand your questions, since there are several
>>> ways to access a linked server. As yourself, as a specific account,
>>> mapping accounts between servers, etc. The commands you might use are:
>>> sp_addlinkedserver
>>> sp_serveroption
>>> sp_addlinkedsrvlogin
>>>
>>> These are also settable through the desktop tools, such as SQL Server
>>> 2000 Enterprise Manager or SQL Server 2005 Management Studio. Although
>>> the position in the object tree is different between the two, the
>>> functionality it pretty much the same.
>>>
>>> If that does not , perhaps you could clarify:
>>> 1 - "run linked servers" - Do you mean access them through the link? Do
>>> you mean what account the other server should be running under?
>>> 2 - "create such account" - See above for commands.
>>> 3 - "what rights" - It depends on the rights your functions need and
>>> (see above) how you choose to connect. If you connect through the
>>> linked server under you own credentials, no other accounts are needed,
>>> but your account will need some rights.
>>>
>>> You did not mention what version you are running, but the Books Online
>>> for your version will discuss linked servers and their security. You
>>> might look for topics like:
>>> Linking Servers
>>> Security for Linked Servers
>>> Configuring Linked Servers for Delegation
>>>
>>> RLF
>>>
>>> "Mark Goldin" <mgoldin@ufandd.com> wrote in message
>>> news:%23ZKplNzDIHA.3848@TK2MSFTNGP05.phx.gbl...
>>>>I am trying to create an SQL Server user that I will use to run linked
>>>>servers.
>>>> How do I create such account? What rights should I give to it?
>>>>
>>>> Thanks
>>>>
>>>
>>>
>>
>>
>
>

Mark,

Oh, you are trying to connect to Visual FoxPro, not another SQL Server. I
should have asked when I saw the "..." in "linkservername...remotetable". I
don't have experience with a non-sysadmin using a linked server to FoxPro.
When I did it, I was using a server job to load tables in SQL Server.
However, looking around in Google, I found:

http://www.eggheadcafe.com/aspnet_an...st27424513.asp
Cindy Winegarden (a Visual FoxPro MVP) had the guidance to allow the
provider to run 'in process' as shown below:

USE [master]
GO
EXEC master.dbo.sp_MSset_oledb_prop N'VFPOLEDB', N'AllowInProcess', 1
GO

HTH,
RLF

"Mark Goldin" <mgoldin@ufandd.com> wrote in message
news:eNEoN7lEIHA.1208@TK2MSFTNGP05.phx.gbl...
> BTW, this is a message I am getting when I am trying to run query using LS
> when I am not a local admin:
> Cannot create an instance of OLE DB provider "VFPOLEDB" for linked server
> "specifications".
>
> "Russell Fields" <russellfields@nomail.com> wrote in message
> news:uUbNEqZEIHA.3916@TK2MSFTNGP02.phx.gbl...
>> Mark,
>>
>> Origin Machine -- The server you issue your query from
>> Linked Server -- The definition on the Origin Server granting an access
>> path the the Target Machine
>> Target Machine -- The server to be accessed from the above linked server.
>>
>> A user account needs no special rights to use a linked server defined on
>> the origin machine. But he does need to have the appropriate rights
>> granted on the target machine. The rights on the target machine are not
>> special to the linked server access either; they simply are the same
>> rights that someone logged on directly to the target machine would need
>> for the functionality to work.
>>
>> However you defined access to be passed through the linked server (you
>> did not mention how you did that) the account that accesses the target
>> server needs rights to the objects it is to access. If they authenticate
>> as themselves, then their login needs rights. If the linked server is
>> defined to force access to a specific account, that account needs the
>> rights.
>>
>> You can secure the linked server to a limited list of people by defining
>> each login that should be able to use the linked server and then setting
>> the linked security definition to deny rights to anybody not on your
>> list. As I understand it, the list must be logins not groups.
>>
>> FWIW,
>> RLF
>>
>> "Mark Goldin" <mgoldin@ufandd.com> wrote in message
>> news:%231eMx%23XEIHA.3712@TK2MSFTNGP02.phx.gbl...
>>> <your account will need some rights.
>>> What are these?
>>> <Do you mean access them through the link?
>>> I am running the following:
>>> select * from linkservername...remotetable
>>>
>>>
>>> "Russell Fields" <russellfields@nomail.com> wrote in message
>>> news:%23agDAWOEIHA.5208@TK2MSFTNGP04.phx.gbl...
>>>> Mark,
>>>>
>>>> I really do not quite understand your questions, since there are
>>>> several ways to access a linked server. As yourself, as a specific
>>>> account, mapping accounts between servers, etc. The commands you might
>>>> use are:
>>>> sp_addlinkedserver
>>>> sp_serveroption
>>>> sp_addlinkedsrvlogin
>>>>
>>>> These are also settable through the desktop tools, such as SQL Server
>>>> 2000 Enterprise Manager or SQL Server 2005 Management Studio. Although
>>>> the position in the object tree is different between the two, the
>>>> functionality it pretty much the same.
>>>>
>>>> If that does not , perhaps you could clarify:
>>>> 1 - "run linked servers" - Do you mean access them through the link?
>>>> Do you mean what account the other server should be running under?
>>>> 2 - "create such account" - See above for commands.
>>>> 3 - "what rights" - It depends on the rights your functions need and
>>>> (see above) how you choose to connect. If you connect through the
>>>> linked server under you own credentials, no other accounts are needed,
>>>> but your account will need some rights.
>>>>
>>>> You did not mention what version you are running, but the Books Online
>>>> for your version will discuss linked servers and their security. You
>>>> might look for topics like:
>>>> Linking Servers
>>>> Security for Linked Servers
>>>> Configuring Linked Servers for Delegation
>>>>
>>>> RLF
>>>>
>>>> "Mark Goldin" <mgoldin@ufandd.com> wrote in message
>>>> news:%23ZKplNzDIHA.3848@TK2MSFTNGP05.phx.gbl...
>>>>>I am trying to create an SQL Server user that I will use to run linked
>>>>>servers.
>>>>> How do I create such account? What rights should I give to it?
>>>>>
>>>>> Thanks
>>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
>