Linked Server Security
- From: JWOL <chairleg@xxxxxxxxx>
- Date: Tue, 30 Oct 2007 09:56:01 -0700
Hi,
I'm having problems querying a linked server. I have set up a linked
server (SQL 2005 x64 SP2) and selected the security option "be made
using the login's current security context" in the object properties.
I can then open the linked server in SSMS and navigate through the
remote databases and see their tables listed. However, if I try to
expand the columns of a remote table I get an error message saying
"SELECT permission was denied on the object all_columns, database
mssqlsystemresource, schema sys". I've checked the permissions on the
all_columns object in the master database and the public role has
select permission on this object. If I right-click on the linked
server and click Test Connection I get "Login failed" for user Domain
\username. Why does this happen when I can browse through the objects
of the server? If I try to run a query such as:
SELECT * FROM [LinkedServer1\inst1].mydb.dbo.mytable
I get the same "Login failed" error message. The same error message
appears with an OPENQUERY query too.
The only way around this I have found is to make the connecting login
a member of the sysadmin role on the linked server. Obviously, this
is not ideal as I only want the login to have read access to one
database. I've tried assigning the login to lower level server roles
and to different database roles too but sysadmin on the server is the
only one that works.
Any ideas please?
Thanks.
.
- Prev by Date: Re: Error: 18488
- Next by Date: Business Admin Permissions
- Previous by thread: Re: Error: 18488
- Next by thread: Business Admin Permissions
- Index(es):
Relevant Pages
|
