-1

Following is the problem I am facing:-

  1. When I run folllowing on ServerA, I get output

        SELECT      tbl.Name, tr.name, c.Text
    FROM      sys.sysobjects tr
          Inner Join sys.sysobjects tbl On tr.parent_obj = tbl.id
          Inner Join sys.syscomments c On tr.id = c.id
    WHERE      tr.xtype = 'TR' And tbl.xtype = 'U'

  2. Problem occurs when I run same query from Server B and refer Server A as a linked server, I get no output.

        SELECT tbl.Name, tr.name, c.Text 
    FROM LinkedServerA.Database.sys.sysobjects tr
    Inner Join LinkedServerA.Database.sys.sysobjects tbl On tr.parent_obj = tbl.id
    Inner Join LinkedServerA.Database.sys.syscomments c On tr.id = c.id
    WHERE tr.xtype = 'TR' And tbl.xtype = 'U'

  3. Even If I run the following I get NULL:

    Select text FROM LinkedServerA.Database.sys.syscomments

Any insight will be much appreciated!

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Ravinder Pal Singh
  • 1
  • 1

1 Answers1

0

This means that your login is mapped to linked server using another login, so you have no permissions to view metadata using linked server login.

You can check your login as well as your db user/roles on linked server using this code:

select * 
from openquery([YourLinkedServer], 'select system_user as linked_srvr_login, 
                             name from YourDB.sys.user_token where principal_id > 0')
sepupic
  • 8,409
  • 1
  • 9
  • 20
  • following is the outcome of the query you asked to run linked_srvr_login name linkedserverlogin linkedserverlogin linkedserverlogin db_datareader – Ravinder Pal Singh Jul 19 '17 at 08:13
  • as you see you are not mapped to linked server with your login name, but with linkedserverlogin that is only a db_datareader. So you just have no permissions to see triggers metadata – sepupic Jul 19 '17 at 08:24