How to create a 'public' synonym

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.

You can't do that with a view.

You could use a stored proc in master to return the results from another db, but naturally that would be directly accessible in a query, you'd have to load it to anther table from the proc first.

Thanks Scott. Are you saying I can't put a synonym over a view? I'm asking this because I have the view working, I just want to mask the database.schema.view from the user, and allow them to get that information while in any of the other databases.

No, I'm saying that a view created in master cannot be dynamically switched to the context of the current db. A proc can do that, but neither a view nor a synonym can.


Why not just create the synonym in every database and have them use that synonym? If it is an issue with new databases - create the synonym in the model database and all new databases will have that synonym included.

A simple cursor over all user databases - and some dynamic SQL will work to create the synonym.

Or - you could just create the view in every database, and again - a simple cursor over all user databases and some dynamic SQL will work to create the view.

1 Like