Linking Servers: dbo on Server #1, user on Server #2

[JulesDBQ]

Can you successfully use linked servers when you are the dbo on "Server #1" and just a reader/user on "Server #2"?

I need to run updates on "Server #1" based on data that is housed on "Server #2". So all I need to do is read the data from the second server.

If this is possible, how do I go about it? I believe that I did the sp_addlinkedservers correctly....but am really unsure of what to do next.

Does anyone have any helpful hints or experience with this? Any help would be much appreciated. Thanks in advance!

 

[TheBugSlayer]

Execute permissions default to members of the sysadmin and setupadmin fixed server roles for sp_addlinkedserver. Make sure you are a member or ask your sys admin to link the servers for you.

You can also add linked servers graphically in Enterprise Manager, in the Security group.

 

[mrdenny]

Yes you can add a link TO a server if you are not a member of the sysadmin fixed server role. However you will need to have a sql account on the second server. Having your domain account have access won't work (unless you are running the code from the console of your SQL Server).

Once you have the linked server setup you need to setup the security mappings. This means mapping your NT account (or what ever account you use to connect to the SQL Server) to the SQL Account on the other machine.

This is done on the security tab in the GUI or via the sp_addlinkedsrvlogin procedure. You can verify that the linked server was created by checking the master.dbo.sysservers table.

Once you have the security setup you can access the other server using the full four part name of the remote object.

select *
from ServerName.DatabaseName.owner.object

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[JulesDBQ]

Thank you for the replies. I was finally able to successfully link to the external SQL Server.

I can run queries on the data, but am unable to save the SQL code for views, stored procedures, etc. I am getting the "OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction" error. I searched on this problem and found quite a few threads in response to it.

I don't know if this is something that I can resolve due to the fact that I have NO power beyond being a reader of the data on the linked server. This linked server is off-site and controlled by a completely different group of people than our server is. I just happen to have access to read the data on it.

Does anyone have any clue how I can successfully save my SQL code for my stored procedures with this linked server involved? How can I get past this error message. Please advise....

 

[mrdenny]

In order to use a distributed transaction you have to have MSDTC enabled on both servers.

Do you have a Begin Transaction or begin Distributed transaction in your code anywhere?

Are you trying to use Enterprise Manager or Query Analyzer to create the view / procedure?

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com