April 26, 2010 at 1:52 pm
I'm trying to extract dimension-data access permissions for all roles in an SSAS database, but there seems to be a discrepancy between what I see in SSMS and what I get with AMO.
Here is an excerpt of the C# code I am using to gather the info (the second nested foreach loop is where I gather the Dimension-Data info):
foreach (Cube cube in db.Cubes)
{
foreach (CubePermission cubePermission in cube.CubePermissions)
{
sql = "INSERT INTO [REPORTING].[dbo].[ssasCubePermission] " +
" ([cube], " +
" [role], " +
" [permission_process], " +
" [permission_read], " +
" [permission_write], " +
" [permission_readDefinition], " +
" [permission_readSourceData]) " +
" VALUES ('" + cube.Name
+ "','" + cubePermission.Role.Name
+ "','" + cubePermission.Process.ToString()
+ "','" + cubePermission.Read.ToString()
+ "','" + cubePermission.Write.ToString()
+ "','" + cubePermission.ReadDefinition.ToString()
+ "','" + cubePermission.ReadSourceData.ToString()
+ "');";
ExecuteSQL(sqlCmd, sql);
//CubeDimensionPermissions
foreach (CubeDimensionPermission cubeDimPermission in cubePermission.DimensionPermissions)
{
sql = "INSERT INTO [REPORTING].[dbo].[ssasCubeDimensionPermission] " +
" ([cube], " +
" [role], " +
" [cubeDimensionID], " +
" [permission_read], " +
" [permission_write]) " +
" VALUES ('" + cube.Name
+ "','" + cubePermission.Role.Name
+ "','" + cubeDimPermission.CubeDimensionID
+ "','" + cubeDimPermission.Read.ToString()
+ "','" + cubeDimPermission.Write.ToString()
+ "');";
ExecuteSQL(sqlCmd, sql);
}
}
}
I'm running the above code within an SSIS script task; when I view the data in the [ssasCubeDimensionPermission] table, I see discrepancies with the same info supplied in SSMS.
Is something wrong in the above code? Anyone have a code snippet for this task?
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
April 26, 2010 at 2:11 pm
Any hints on the discrepancies? (ie something is there in SSMS but not in the output of your insert; or perhaps the permissions themselves differ slightly) Not knowing the discrepancies it's a little hard to assist you in debugging the issue.
Steve.
April 26, 2010 at 2:45 pm
stevefromOZ (4/26/2010)
Any hints on the discrepancies? (ie something is there in SSMS but not in the output of your insert; or perhaps the permissions themselves differ slightly) Not knowing the discrepancies it's a little hard to assist you in debugging the issue.
Let's start with what is shown in SSMS. I right-click on one of my custom roles, select Properties and click on Dimension Data.
I don't think I understand what I see there.
I see a list of users with checkboxes next to them, which I guess refer to access to the dimension selected at the top of the dialog.
Does that access mean read, write or both? It is not clear.
When I check the database table where the permissions are recorded in script above, the same role does not have any permissions on that dimension.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
April 26, 2010 at 4:19 pm
Let's start with what is shown in SSMS. I right-click on one of my custom roles, select Properties and click on Dimension Data.
I don't think I understand what I see there.
I see a list of users with checkboxes next to them, which I guess refer to access to the dimension selected at the top of the dialog.
Does that access mean read, write or both? It is not clear.
Ok, couple of things, first up, the list of 'users', are you sure these are not attributes within your dimension? All User info should be contained in the Membership tab of the role dialog. Assuming that this is maybe a User Attribute within a MyUsers dimension, this tree of checkboxes is letting you determine which datapoints (ie members) within any given Attribute Hierarchy tha this role can (or cannot) access.
When I check the database table where the permissions are recorded in script above, the same role does not have any permissions on that dimension.
I think this is because you're looking at the DimensionPermissions object, whereas the Attribute permissions I mentioned above are contained in the AttributePermission class.
Steve.
April 27, 2010 at 2:57 pm
stevefromOZ (4/26/2010)
Let's start with what is shown in SSMS. I right-click on one of my custom roles, select Properties and click on Dimension Data.
I don't think I understand what I see there.
I see a list of users with checkboxes next to them, which I guess refer to access to the dimension selected at the top of the dialog.
Does that access mean read, write or both? It is not clear.
Ok, couple of things, first up, the list of 'users', are you sure these are not attributes within your dimension? All User info should be contained in the Membership tab of the role dialog. Assuming that this is maybe a User Attribute within a MyUsers dimension, this tree of checkboxes is letting you determine which datapoints (ie members) within any given Attribute Hierarchy tha this role can (or cannot) access.
When I check the database table where the permissions are recorded in script above, the same role does not have any permissions on that dimension.
I think this is because you're looking at the DimensionPermissions object, whereas the Attribute permissions I mentioned above are contained in the AttributePermission class.
Yes, thank you for the reply, it has cleared up a couple of things for me.
I was confused as to the 1st point, but now I understand that these are dimension members not role members.
I will check into the AttributePermission class, if you have any code on this, it'd be appreciated.
Thanks again!
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
April 27, 2010 at 3:20 pm
Sorry, no code of my own but there's definitely code out there on MSDN (more piecemeal than whole parts) but googling definitely returns other code samples/examples.
Steve.
April 28, 2010 at 7:57 am
Thanks, I'll look around on the web.
It's frustrating that BOL has barely no code samples in the sections that describe the Analysis Services classes.
I find links like the one below utterly useless:
http://technet.microsoft.com/en-us/library/microsoft.analysisservices.dimensionpermission.aspx
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
April 28, 2010 at 9:14 am
This seems like a good starting point:
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply