title: Granting access to one db to users/roles of another
body: <p><strong>Short version:</strong> Can I grant access to external databases to a role?</p>
  
  <p><strong>Long version:</strong></p>
  
  <p>I am working on reports using Crystal which is retrieving data from an applications SQL Server Instance (<code>database1</code>).</p>
  
  <p>The application is running the reports and overwriting the connection in the report, I have no access to the applications code.</p>
  
  <p>I have added a new DB to the server (<code>database2</code>) which is collecting information from a telephone exchange and I want to join some of this information to the applications data (<code>database1</code>).</p>
  
  <p>I can join the data and the reports work when run within the designer (logged in as SA) but when the reports are run externally through the application they fail with a fairly generic error (Failed to retrieve data).</p>
  
  <p>I am assuming the error is being caused by the new DB permissions as if I log into the application as SA the error goes away.</p>
  
  <p>The Application has a special DB Role for users that run reports, when adding a table/view/sp to the application db (<code>database1</code>) I can simply grant select/execute to this role to allow the reports to access the object.</p>
  
  <p>Now I have object in a different db however the role isn't easily accessible.</p>
  
  <p>Is there any way I can reference the second db (<code>database2</code>) through the existing role?</p>
  
  <p>eg:</p>
  
  <pre><code>USE [database1]
  GRANT EXECUTE ON [database2].[dbo].[CUSTOM_PROCEDURE] TO [applicationrole1] 
  
  OR
  
  USE [database2]
  GRANT EXECUTE ON [dbo].[CUSTOM_PROCEDURE] TO [database1].[dbo].[applicationrole1]
  </code></pre>
  
  <p>Ideally I want to be able to link to the Role somehow rather than re-creating a new role as the role is updated by the application regularly when new users are added/configured.</p>
  
  <p><em>(Not tagged with Crystal-Reports as this isn't related to the problem)</em></p>
  
  <p><strong>Edit:</strong></p>
  
  <p>Is there any way to do something like:</p>
  
  <pre><code>INSERT INTO Database2.sys.database_principals
  SELECT * FROM Database1.sys.database_principals
  WHERE [type] = 'S'
  </code></pre>
  
  <p>To copy over the Users (not logins) and then add the role members?</p>