May 12, 2011 at 2:24 pm
I have a database (Production) where all the users have read and write permissions and they belong to a role (ProdUsers). I have created another database for auditing (Audit). I created a view of the Audit data and placed that view in the Production db. When I run the view as an admin, everything works fine, but when I run the view as a user, it does not work. Obviously, I need to grant permission to the users. How do I grant permission to the role ProdUsers in the Production db so that they can read the data in the Audit db without needing to maintain two separate database securities?
Thanks
May 12, 2011 at 4:35 pm
The short answer is, "You can't." Each databases security is independent.
There are really two ways to handle this:
Ownership Chaining:
You can turn on ownership chaining on both databases (don't do it server wide). Like ownership chaining within a database, if the objects are owned by the same owner (in this case SQL Server maps the owners up to the login level) then an ownership chain can form. However, the user still needs access into that second database. If your views are doing some sort of filtering, especially if it's security related, it's really your only choice. Make sure you understand the implications of using ownership chaining first, though, because it is considered a weakening of security overall.
Permissions on the Dependent Objects:
The other option is to manage the permissions separately. If you have views in one database, you will have to grant the appropriate access to the objects they reference in the other. Obviously this means the users can access the other database and the other database objects directly.
K. Brian Kelley
@kbriankelley
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply