What's a good way to model user authorization to hierarchical data? I'm wanting to implement something that's basically an ACL in SQL Server (2008, we'll be moving to 2012).  We're working with farm production information.  We will have users who need to be authorized to view production records, but only at certain vertical levels in a hierarchy.

Our data hierarchy looks like this:

System
- Farm
  - Group
    - Animal


The idea is that some users will have access at the System level, and can see records for all Farms, Groups, and Animals within that System.  Likewise, some users will have permission starting at the Farm level, and need access only linked to that Farm and all Groups (and animals) within it.

Each table contains a primary key column, and a foreign key column linking it to the parent record (along with whatever other attributes each entity requires).

What I've implemented in the past is two-table system for linking users to the appropriate items they're allowed to see.  Implemented here, it would look like this:

Table:  Authorizations          Table:  FullAuthorizations
Columns:    Id (PK)             Columns:    Id (PK)
            UserId                          UserId
            ObjectId                        SystemId
            ObjectType                      FarmId
                                            GroupId
                                            AnimalId


The application inserts a record into Authorizations, with the user to authorize, the record id (System id, Farm id, etc), and the type of record (System, Farm, etc).  The FullAuthorizations table is used to denormalize the farm hierarchy for easier/faster filtering of data.  A trigger is used on the Authorizations table (and each of the farm, etc, tables) to update FullAuthorizations.  I considered using a View here, in a previous project with additional levels of entities, and the performance was quite poor once we began getting several hundred thousand records.

The queries would look something like:

SELECT *
FROM dbo.Animals a
WHERE EXISTS (
    SELECT 1
    FROM dbo.FullAuthorizations fa
    WHERE fa.UserId = 1 AND fa.AnimalId = a.Id
)


In the other project where we're doing this, the solution is performant, but feels like a bit of a hack, and I especially don't like that we can't maintain referential integrity on Authorizations with the associated objects.  I'd appreciate feedback on some other possible solutions.  I've been looking at things like Nested Sets, but not sure something like that fits this particular problem.
A long time ago, in 2000, I had a similar question, and posted on Google Groups. I received an awesome response from a guy called Joe Celko, and it will fit right into your problem. I've been using this methodology ever since, and it has great performance, and works really well

https://groups.google.com/forum/#!search/pascaljr/microsoft.public.sqlserver.programming/knQ5WXIDfeo/E0vfeC2kbTsJ

It uses two integer values, a left and a right value, to store the hierarchy. I've made some adaption to the table and procs over the years, but the essence remains the same.