Linked Server Security

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.

.

Relevant Pages

  • Re: Install MSDE w/ MSDE Depl.Toolkit. What permissions when using Win Auth?
    ... I created a login using Enterprise Manager on SQL Server on my server. ... Database Access tab I ticked the tick box for the database that I ... Now when my user installed the MSDE database on his machine locally, ...
    (microsoft.public.sqlserver.msde)
  • Re: Installation OK, but cant connect
    ... > created an access database. ... SQL Server authentication is "client" independent.... ... connections or SQL Server authenticated connections... ... which authenticate user's login at the windows login step... ...
    (microsoft.public.sqlserver.msde)
  • Re: Setting up Linked server to MsAccess
    ... At the server level I have added my windows user group ... At the database level I have added my Server login ...
    (microsoft.public.sqlserver.security)
  • Re: SQL2005: Cannot connect error 11001
    ... user mapped to one database. ... Does the issue has to do with the login account / user ... Server connection. ... if you changed the port ...
    (microsoft.public.sqlserver.connect)
  • Re: Sql Server Login
    ... > When i'm using Server Explorer, I have a small red x next to all the ... > Which is better for creating the database on one computer and then ... login credential you need to, you have to set the SQL Server/MSDE instance ... SQL Server uses a so called "2 phase" authentication policy: ...
    (microsoft.public.sqlserver.msde)