March 27, 2008 at 7:28 pm
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 ?
April 1, 2008 at 11:22 pm
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...:)
April 3, 2008 at 8:55 am
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
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply