Probably asked and answered 100 times but I'm stuck.
Have a production database and I need to give a third party access to a subset of data via views. I do not want them to have access to the underlying tables as that would defeat the purpose.
db1 has table [Location]
db2 has view [Location]
Database Owner of both db1 and db2 is the same login.
Schema of both [location] objects is dbo, schema owner is DBO.
user myLogin exists in both db1 and db1
myLogin has been granted Select permission on view [Location]
table [Location] has permissions assigned to two custom Database Roles of which myLogin is not a member
select * from location fails
Msg 229, Level 14, State 5, Line 1
The SELECT permission was denied on the object 'Location'