I have one instance with 8 user databases. Each of those databases is for a similar purpose with an installed 3rd-party tool that has metadata.
In the master database I've created a view that unions the same table from each database. Now I would like to create a synonym in the master database and have it available to anyone using any of the databases, but without having to specify [master].[dbo].registry_v.
For example, in [master] I have a view dbo.registry_v.
Another user 'CREATOR', while in database RPT, I'd like them to be able, from anywhere, SELECT * FROM REGISTSRY_V and not have to worry about the database name and schema.
I have a synonym created in [master], but I can't grant select on it to CREATOR, but he only exists in RPT. And the synonym is still in [master] so it's not helping me much.
I think I'm missing something, any help would be appreciated.