Access to views via linked server and synonyms issue

• cantor

  Say Hey Kid

  Points: 667

  More actions

  March 27, 2008 at 7:28 pm

  #187906

  Here is a description of the problem I am having, I have included as much info as I can think of but I'm not a security expert so if I've missed anything let me know.

  Server 1 has database A

  Server 2 has database B and C

  working backwards...

  Database C is a vendor database with a schema 'our_schema' set aside for holding views that are specific to our site

  eg. our_schema.our_view

  the view 'our_schema.our_view' has select privileges granted by dbo to role 'our_role'

  Account 'our_account' exists in both database B and C and is a member of role 'our_role' in both

  Database B has a number of synonyms that allow us to access the views in database C like this:

  CREATE SYNONYM dbo.our_view FOR C.our_schema.our_view

  GO

  GRANT SELECT ON dbo.our_view TO our_role

  GO

  The only difference in permissions between database B and C for our_account is that in database C it belongs to the fixed database role db_datareader

  Database A has a linked server set up to talk to database B on Server 2 thus...

  EXEC master.dbo.sp_addlinkedserver @server = N'LinkedServerToServer2', @srvproduct=N'LinkedServerToServer2', @provider=N'SQLNCLI', @datasrc=N'Server2', @catalog=N'B'

  The context under which the linked server runs is 'our_account'

  Problem description...

  If I log into database C under our_account and issue this...

  select top 10 * from our_schema.our_view

  I get a result set back

  If I log into database B under our_account and issue this...

  select top 10 * from dbo.our_view -- (which in turn calls C.our_schema.our_view)

  I get a result set back

  If I log into database A under 'any_old_account' and issue this...

  select top 10 * from LinkedServerToServer2.C.our_schema.our_view

  it returns a result set

  If I log into database A under 'any_old_account' and issue this...

  select top 10 * from LinkedServerToServer2.B.dbo.our_view

  I get

  Msg 7357, Level 16, State 2, Line 1

  Cannot process the object ""B"."dbo"."our_view"". The OLE DB provider "SQLNCLI" for linked server "LinkedServerToServer2" indicates that either the object has no columns or the current user does not have permissions on that object.

  Summary: I can access the view in database C via the linked server but not when I try to get to it via the synonyms in database B.

  Now account 'our_account' has select access to this view via membership of the role 'our_role' in database C (although it does not have select access to all views in database B because it is not a member of the role db_datareader) so the above should work right ?

  I've had a look around but can't find anyone else with a similar issue.

  Any pointers as to what the source of the problem is ?

• cantor

  Say Hey Kid

  Points: 667

  More actions

  April 1, 2008 at 11:22 pm

  #797022

  Answer:

  http://msdn2.microsoft.com/en-us/library/ms190626.aspx

  "You cannot reference a synonym that is located on a linked server."

  Heigh-ho back to using OPENQUERY...:)

• Key DBA

  SSCertifiable

  Points: 6029

  More actions

  April 3, 2008 at 8:55 am

  #797877

  UncleJimBob,

  Thanks for posting your solution, I will tuck it away in my IE Favorites for future reference.

  Regards,

  "Key"
  MCITP: DBA, MCSE, MCTS: SQL 2005, OCP