Identifying Predefined Roles on SQL Server Reporting Services 2005 (SSRS)
I was given the task of writing some code to add a user or group to a role on a SQL 2005 Express Edition Reporting Services server.
The natural answer to the problem of giving users access to a Reporting Services server is to create your own roles for which only the required Tasks are allowed.
Creating roles is not allowed as it is a limitation of Express Edition. So - your best bet is to find the most suitable predefined role to assign your user / group to.
The single biggest issue I found in trying to do this was identifying the predefined roles.
As a result of calling GetRoleProperties() on each of the roles defined on my server I got the following list of Task IDs.
Content Manager
E96A7928-F3A5-409d-A6AA-0808172B28CB
3246FDF7-16FB-4802-ABFA-76D4F4A8AD62
E2723F22-E29C-496b-B981-1D775F45FC09
88552A24-99BA-46ab-90CD-EF66FD3E444D
993C580B-3FBF-444b-B85E-A8DA50ADF40F
1D574E69-B01D-4278-A25A-DEE6B3790F81
2FBF7AE5-0DB6-46f2-B9BB-480794FBC97E
683665AB-EE4B-4026-99AE-68A9899F8B6E
75D856B8-2FC1-41c9-8DFA-FE0FF6153C20
F95F31D0-834A-4c0e-B290-E3E4477908CA
7813C99B-3F84-4d02-930F-72FA3B86024A
B96D28BB-23D0-4a32-B57A-7C12EBDD0704
AD4523AD-09B6-46ab-A7F0-AD1F52449FE1
A1BDCA29-F891-418f-BEAA-43E937F800C4
F59546B7-49A1-41de-8E9A-34137F3D677F
4BF862B5-10D0-4793-B075-802F7775561E
Publisher
3246FDF7-16FB-4802-ABFA-76D4F4A8AD62
88552A24-99BA-46ab-90CD-EF66FD3E444D
1D574E69-B01D-4278-A25A-DEE6B3790F81
683665AB-EE4B-4026-99AE-68A9899F8B6E
AD4523AD-09B6-46ab-A7F0-AD1F52449FE1
F59546B7-49A1-41de-8E9A-34137F3D677F
Report Builder
E2723F22-E29C-496b-B981-1D775F45FC09
993C580B-3FBF-444b-B85E-A8DA50ADF40F
2FBF7AE5-0DB6-46f2-B9BB-480794FBC97E
F95F31D0-834A-4c0e-B290-E3E4477908CA
A1BDCA29-F891-418f-BEAA-43E937F800C4
4BF862B5-10D0-4793-B075-802F7775561E
System Administrator
A7E17983-FFC5-4252-9EBC-DF5C495A4CFA
075BE56B-589C-47ed-B072-5B5EDCF80C66
DED0947B-E39C-4a03-A512-10AF2933772B
7663B035-0C99-488d-942B-7B36345178EB
EC6EE51B-0D96-478b-8477-826AA7637B68
B8326178-0D1A-4054-B591-1CE98F492FCF
Browser
E2723F22-E29C-496b-B981-1D775F45FC09
993C580B-3FBF-444b-B85E-A8DA50ADF40F
2FBF7AE5-0DB6-46f2-B9BB-480794FBC97E
F95F31D0-834A-4c0e-B290-E3E4477908CA
A1BDCA29-F891-418f-BEAA-43E937F800C4
System User
D07A969D-0B11-4a4a-8685-7E0E6DEEBA36
20278B55-A582-4926-8EB6-5C0D27CED8F9
B8326178-0D1A-4054-B591-1CE98F492FCF
Model Item Browser
69383E55-A810-41f4-8A89-69A0D5976F49
I have written a RoleIdentifier class that has a method on it that returns an enumeration which contains a value for each of the predefined roles, or ‘Unknown’ if the role cannot be identified. There are undoubtedly some shortcomings in this approach - for example, if you use the same class to identify roles on a server where role creation is allowed then you may end up falsely identifying a user defined role as a predefined role. It is my best guess that this is safe to use against an Express Edition server.
Here’s the code:
enum PredefinedReportingServiceRoles
{
Browser,
ContentManager,
ModelItemBrowser,
MyReports,
Publisher,
ReportBuilder,
SystemAdministrator,
SystemUser,
Unknown
}
class RoleIdentifier
{
Role role;
ReportingService2005 rs;
public RoleIdentifier(ReportingService2005 rs, Role role)
{
this.rs = rs;
this.role = role;
}
public PredefinedReportingServiceRoles GetPredefinedRole()
{
string description;
Task[] roleTasks = rs.GetRoleProperties(role.Name, out description);
if (AreRequiredTasksPresent(contentManagerGuids, roleTasks))
return PredefinedReportingServiceRoles.ContentManager;
if (AreRequiredTasksPresent(publisherGuids, roleTasks))
return PredefinedReportingServiceRoles.Publisher;
if (AreRequiredTasksPresent(reportBuilder, roleTasks))
return PredefinedReportingServiceRoles.ReportBuilder;
if (AreRequiredTasksPresent(systemAdministrator, roleTasks))
return PredefinedReportingServiceRoles.SystemAdministrator;
if (AreRequiredTasksPresent(browser, roleTasks))
return PredefinedReportingServiceRoles.Browser;
if (AreRequiredTasksPresent(systemUser, roleTasks))
return PredefinedReportingServiceRoles.SystemUser;
if (AreRequiredTasksPresent(modelItemBrowser, roleTasks))
return PredefinedReportingServiceRoles.ModelItemBrowser;
return PredefinedReportingServiceRoles.Unknown;
}
private bool AreRequiredTasksPresent(Guid[] predefinedTaskGuids, Task[] roleTasks)
{
foreach (Guid taskGuid in predefinedTaskGuids)
{
bool taskGuidInRole = false;
foreach (Task roleTask in roleTasks)
{
if (taskGuid.Equals(new Guid(roleTask.TaskID)))
taskGuidInRole = true;
}
if (!taskGuidInRole)
return false;
}
return true;
}
private readonly Guid[] contentManagerGuids = new Guid[] {
new Guid("E96A7928-F3A5-409d-A6AA-0808172B28CB"),
new Guid("3246FDF7-16FB-4802-ABFA-76D4F4A8AD62"),
new Guid("E2723F22-E29C-496b-B981-1D775F45FC09"),
new Guid("88552A24-99BA-46ab-90CD-EF66FD3E444D"),
new Guid("993C580B-3FBF-444b-B85E-A8DA50ADF40F"),
new Guid("1D574E69-B01D-4278-A25A-DEE6B3790F81"),
new Guid("2FBF7AE5-0DB6-46f2-B9BB-480794FBC97E"),
new Guid("683665AB-EE4B-4026-99AE-68A9899F8B6E"),
new Guid("75D856B8-2FC1-41c9-8DFA-FE0FF6153C20"),
new Guid("F95F31D0-834A-4c0e-B290-E3E4477908CA"),
new Guid("7813C99B-3F84-4d02-930F-72FA3B86024A"),
new Guid("B96D28BB-23D0-4a32-B57A-7C12EBDD0704"),
new Guid("AD4523AD-09B6-46ab-A7F0-AD1F52449FE1"),
new Guid("A1BDCA29-F891-418f-BEAA-43E937F800C4"),
new Guid("F59546B7-49A1-41de-8E9A-34137F3D677F"),
new Guid("4BF862B5-10D0-4793-B075-802F7775561E") };
private readonly Guid[] publisherGuids = new Guid[] {
new Guid("3246FDF7-16FB-4802-ABFA-76D4F4A8AD62"),
new Guid("88552A24-99BA-46ab-90CD-EF66FD3E444D"),
new Guid("1D574E69-B01D-4278-A25A-DEE6B3790F81"),
new Guid("683665AB-EE4B-4026-99AE-68A9899F8B6E"),
new Guid("AD4523AD-09B6-46ab-A7F0-AD1F52449FE1"),
new Guid("F59546B7-49A1-41de-8E9A-34137F3D677F") };
private readonly Guid[] reportBuilder = new Guid[] {
new Guid("E2723F22-E29C-496b-B981-1D775F45FC09"),
new Guid("993C580B-3FBF-444b-B85E-A8DA50ADF40F"),
new Guid("2FBF7AE5-0DB6-46f2-B9BB-480794FBC97E"),
new Guid("F95F31D0-834A-4c0e-B290-E3E4477908CA"),
new Guid("A1BDCA29-F891-418f-BEAA-43E937F800C4"),
new Guid("4BF862B5-10D0-4793-B075-802F7775561E") };
private readonly Guid[] systemAdministrator = new Guid[] {
new Guid("A7E17983-FFC5-4252-9EBC-DF5C495A4CFA"),
new Guid("075BE56B-589C-47ed-B072-5B5EDCF80C66"),
new Guid("DED0947B-E39C-4a03-A512-10AF2933772B"),
new Guid("7663B035-0C99-488d-942B-7B36345178EB"),
new Guid("EC6EE51B-0D96-478b-8477-826AA7637B68"),
new Guid("B8326178-0D1A-4054-B591-1CE98F492FCF") };
private readonly Guid[] browser = new Guid[] {
new Guid("E2723F22-E29C-496b-B981-1D775F45FC09"),
new Guid("993C580B-3FBF-444b-B85E-A8DA50ADF40F"),
new Guid("2FBF7AE5-0DB6-46f2-B9BB-480794FBC97E"),
new Guid("F95F31D0-834A-4c0e-B290-E3E4477908CA"),
new Guid("A1BDCA29-F891-418f-BEAA-43E937F800C4") };
private readonly Guid[] systemUser = new Guid[] {
new Guid("D07A969D-0B11-4a4a-8685-7E0E6DEEBA36"),
new Guid("20278B55-A582-4926-8EB6-5C0D27CED8F9"),
new Guid("B8326178-0D1A-4054-B591-1CE98F492FCF") };
private readonly Guid[] modelItemBrowser = new Guid[] {
new Guid("69383E55-A810-41f4-8A89-69A0D5976F49") } ;
}
… and here is an example of how to use it.
foreach (Role role in reportingService.ListRoles(SecurityScopeEnum.All))
{
RoleIdentifier roleIdentifier = new RoleIdentifier(reportingService, role);
Console.WriteLine(role.Name);
string description;
Task[] tasks = reportingService.GetRoleProperties(role.Name, out description);
if (roleIdentifier.GetPredefinedRole() == PredefinedReportingServiceRoles.SystemAdministrator)
sysAdminRole = role;
if (roleIdentifier.GetPredefinedRole() == PredefinedReportingServiceRoles.ContentManager)
contentManagerRole = role;
}
Have fun!
Write a comment