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.
Sherrie
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.
2 Likes
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