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!

About Adam

Speak Your Mind

Tell us what you're thinking...
and oh, if you want a pic to show with your comment, go get a gravatar!