The nightly process in our production system collects data from our real time systems (JD Edwards) and then processes that data. Many of these process manipulate the data and supply a modified data set for use by the dozens of client processes and applications. All of the client applications have their own databases (tables, views, procedures, etc), but due to the nature of the 'raw' data we collect (we have over 350 million rows of historical and current data) sometimes the client apps need to directly access this. It would be too costly to replicate this data for each individual application that would need it as well as maintaining dozens of copies would be impossible. This posed a unique problem: How do you grant access to the raw data in limited format?
The reason for the limited format is some of the data is for the ETL process only and if used by an application would provide false or erroneous data (one of our tables has over 250 columns). After a bit of research we came up with the following mechanism to allow specific access to specific tables in specific databases, with the added benefit of not having to add every new id to the permission roster.
First thing that needs to be done is to make sure that the same owner owns the files of the databases you are looking to chain. To simplify our servers, all files are owned by SA. This prevents the situation where a user id was used to create or restore a database, but they are no longer with us. Here is where you set that up:
Second thing we need to do is turn on 'database cross-chaining'. This enables permissions to be transferred when you have statements that cross databases, such as being in database A and doing the following: select * from B.dbo.sometable. This causes sql server to validate the user who is in database A to have permission to select from the table 'sometable' in database B. You turn on database cross chaining as follows: sp_dboption
Third thing to be done is: grant connect to guest in both databases. This is the mechanism that SQL Server uses to pass credentials.
The next step is we create views in the 'source' database, which in the above example would be in the B database. Then you create a corresponding view in the application database, A. This does two things. First as you add users to the application databases you need not add them to any of the source databases, since they will be calling local views. Second, it provides a way to seal off the applications and as we add new sources of information, we simply create local views and hence isolate all applications from the Common data, keeping it pristine and from prying eyes.
I do realize the applications have to go through two views, the local view and then the view in the source database. Some have asked if this has a noticeable performance impact and I cannot say it does. The security flexibility we get from the above design far out ways any performance detriment as the apps run well within the time allotted (many sub-second).