This entry continues the discussion started in “Where’s the beef? (eh, permissions?), Part 1”.
Instead of removing the permissions from each object’s XML file, I decided I would just recreate the roles. I created a new Sales role and a new HR role. When I clicked on the Cube Access tab in the Role Designer, I found out that the Sales role had Read permission on the HR cube and no permission on the Sales cube. The HR role had Read permission on the Sales cube and no permission on the HR cube.
How did this happen? In my original database I first created the HR role and AS 2005 behind the scenes assigned it Object ID = Role. I then created the Sales role and it was assigned Object ID = Role 1. Then when permissions were granted to objects in the original database, the permissions referenced the roles by their Object IDs. You guessed it, in my new database I created the Sales role first and then the HR role, so when AS 2005 assigned the Object IDs they were exactly opposite to the IDs that were assigned in my original database.
Lesson learned? Objects are portable between databases, just be REALLY careful about the permissions granted/denied to each object.
- Scott