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 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy