RE: Linked Databases
- From: Chuck P <Chuck@xxxxxxxxxxxxxxxx>
- Date: Thu, 9 Nov 2006 06:17:02 -0800
This was more of a best practices question. When I have lots of
dbs/applications on servers and they needs to read data from another server
(enterprise data). What is the best practice from a configuration management
standpoint to grant access to the remote data.
I was hoping to limit the permission assignments and account generatrion on
the enterprise data server(sql2000). I wanted to do that because lots of
applications/other servers need to access the enterprise data (sql2000).
There is no way I will every assign dbreader to an account to read data on
the linked server. That would allow them to read lots of tables they have no
buisiness seeing. I don't like the administrative headaches of setting up
and maintaining sql accounts on multiple servers.
"Charles Wang[MSFT]" wrote:
Hi Chunk,.
From your description, I understand that:
You wanted to retrieve data on your SQL Server 2005 from your SQL Server
2000.
You had created a linked server to the SQL Server 2000 with a sql login,
however you found that it did not work. You want to know what the best way
is to grant the
data access without having permissions spewed all over the place.
If I have misunderstood, please let me know.
I performed a test but unfortunately could not reproduce your issue. I
write my test steps here and hope that you can confirm if it is helpful to
your scenario:
Intent
============================
Access the table [orders] in the Northwind database of MyServer2\S2000 from
MyServer1\SQL2K5.
Server Conditions
============================
Server1:
OS: Windows 2003 Ent Edition SP1
SQL Server: SQL Server 2005 Ent SP1
Instance Name: MyServer1\SQL2K5
Server2:
OS: Windows 2003 Ent Edition SP1
SQL Server: SQL Server 2000 Ent SP4
Instance Name: MyServer2\S2000
Test Steps
============================
1. On Server2, create a SQL login 'usr_test' under MyServer2\S2000 and
assign it with public, db_datareader and db_datawriter permissions on the
Northwind database;
2. On Server1, ensure you can access MyServer2\S2000 with the SQL login
usr_test;
3. On Server1, use sp_addlinkedserver and sp_addlinkedsrvlogin to add
MyServer2\S2000 as a linked server:
use master
go
exec sp_addlinkedserver 'MyServer2\s2000',N'SQL Server'
exec sp_addlinkedsrvlogin 'MyServer2\s2000','false',
NULL,'usr_test','Password!'
After above steps, I can use any login account to execute the SQL
statements without having permissions spewed all over the place:
Update [sha-chlwang-03\s2000].Northwind.dbo.orders set ShipRegion='RJ'
where OrderID=10248
select * from [MyServer2\s2000].Northwind.dbo.orders
As you can see, I just create a login account with read and write
permissions under the SQL Server 2000. No extra permissions need to be
assigned here.
Hope this helpful. Please feel free to let me know if you have any other
questions or concerns.
Sincerely yours,
Charles Wang
Microsoft Online Community Support
======================================================
When responding to posts, please "Reply to Group" via your newsreader
so that others may learn and benefit from this issue.
======================================================
Your posting back to let us know the issue status is greatly appreciated.
======================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================
- Follow-Ups:
- RE: Linked Databases
- From: Charles Wang[MSFT]
- RE: Linked Databases
- From: Charles Wang[MSFT]
- RE: Linked Databases
- References:
- Linked Databases
- From: Chuck P
- RE: Linked Databases
- From: Charles Wang[MSFT]
- Linked Databases
- Prev by Date: Re: Monitor add\remove logins or role membership
- Next by Date: RE: Linked Databases
- Previous by thread: RE: Linked Databases
- Next by thread: RE: Linked Databases
- Index(es):
Relevant Pages
|
|
