Linked server

• Casper101

  Hall of Fame

  Points: 3903

  More actions

  May 27, 2008 at 3:51 am

  #186143

  Hi,

  I am trying to execute the following:

  SELECT * FROM [LinkedSeverName].master.sys.sysaltfiles

  But get this error:

  Cannot process the object ""master"."sys"."sysaltfiles"". The OLE DB provider "SQLNCLI" for linked server "LinkedSeverName" indicates that either the object has no columns or the current user does not have permissions on that object.

  I can confirm that the linked server has been set up correctly, as I can SELECT from other user tables on that linked server.

  I can also execute statements like these successfully:

  SELECT * FROM [LinkedSeverName].master.sys.tables

  SELECT * FROM [LinkedSeverName].master.sys.databases

  Why can I not SELECT from sys.sysaltfiles table?

  Thanks,

  M

• jasmin.tang

  SSC Rookie

  Points: 27

  More actions

  May 28, 2008 at 6:46 pm

  #821523

  Hi,

  Try specifying the object owner, something like this, which works fine for me:

  SELECT * FROM [LinkServerName].master.dbo.sysaltfiles

  Hope you find it helpful.

  Regards,

  Jasmin Tang

• Casper101

  Hall of Fame

  Points: 3903

  More actions

  May 29, 2008 at 1:22 am

  #821613

  Hi Jamsin,

  I have tried you suggestion, but no luck - I still receive the same error.

  Hmmmm, this is strange

• jasmin.tang

  SSC Rookie

  Points: 27

  More actions

  May 29, 2008 at 3:06 am

  #821639

  Hi,

  Try this,

  SET FMTONLY OFF

  SELECT * FROM [LinkedSeverName].master.sys.sysaltfiles

  Hope you find it helpful.

  Regards,

  Jasmin Tang

• Casper101

  Hall of Fame

  Points: 3903

  More actions

  May 29, 2008 at 3:16 am

  #821643

  If I go to Server objects - Linked Servers - LinkedServerName - Catalogs - System Catalogs - master - Systems Views, then the sys.sysaltfiles is listed. BUT when I right click and script view as SELECT, then I get the same error.

  So I think your previous suggestion might be correct - about the users not having access/permission.

  What permissions should this user have to access those system views. As stated previously some of those views are accessible, but not this one specifically.

  Thanks for your help so far 🙂

  M