I have a database server (Sql Server 2008 R2 Enterprise) supporting two separate but related vendor products, each with it's own database (call them X and Y)
In database X I a have a lot of users defined, but their access rights come down to a manageable handful of roles. I'd like to take one of these roles and use it to grant select access to a few tables in database Y. I really want to avoid creating a new role in database Y and re-adding all the users, not just because of the busy-work setting up that security info, but also because of needing to keep these roles in sync over time, as users come and go. Really, the role that already exists in database X already does a great job of logically defining the access group I want in database Y from a business standpoint, and I want to keep it that way.
How can I grant access in Y for this role from X?