November 5, 2010 at 2:20 pm
I created a linked server to a remote SQL Server. Both local and remote are 2008 R2. Local is SQLExpress, btw.
I can execute (names changed to protect the innocent):
exec RemoteServer.RemoteDB.dbo.sp_executesql N'SELECT COUNT(*) FROM myTable'
successfully and get the results. But if I execute
SELECT COUNT(*) FROM RemoteServer.RemoteDB.dbo.myTable
I get a 7314 error.
I have very low permissions on the external database, probably db_reader on that database only, with no permissions on the system databases. For example, I can't browse the tables I have access to in Management Studio. I don't know if this is part of the problem or not.
Any ideas on how to diagnose this? My sp_addlinkedserver is pretty straight-up and I think is validated by the fact the remote sp_executesql works. I do have the (SQL Server) user name and password in @provstr.
November 5, 2010 at 3:04 pm
Are you using a static login on your linked server? Impersonating? If you log into the remote server directly using those credentials, are you allowed to select from the table?
November 5, 2010 at 5:07 pm
Yes, static login. Not impersonating. If I connect directly through management studio the same query works fine
November 8, 2010 at 11:30 am
Anyone?
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply