Post reply

Linked server

  • 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

  • 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

  • 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

  • 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

  • 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

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply