Post reply

user list and permissions

  • February 7, 2011 at 9:09 am

    #233970

    Hi

    I have created a report within BIDS to show a list of users and which folder they have permissions to on the SSRS site.

    Here is the coding I have

    select

    dbo.Users.UserName,dbo.Roles.RoleName,substring(dbo.catalog.path,2,LEN(dbo.catalog.path)) FolderName

    from dbo.PolicyUserRole

    left join dbo.Users on dbo.Users.UserID=dbo.PolicyUserRole.UserID

    left join dbo.Roles on dbo.Roles.RoleID=dbo.PolicyUserRole.RoleID

    inner join dbo.Catalog on dbo.Catalog.PolicyID=dbo.PolicyUserRole.PolicyID

    where TYPE=1

    I have grouped on UserName which gives me the infomation I need but it shows me the UserName,then a list of folders they have access which is fine its the role which is giving me problems, for each folder it is showing me each permission as in content manager, my reports, report builder etc, I would prefer it to be a list across seperated by commas rather than a list down as it shows me for example.

    User &nbsp &nbsp &nbsp Folder &nbsp &nbsp &nbsp Role

    JBloggs &nbsp Accounts Report builder

    &nbsp &nbsp &nbsp &nbsp &nbsp Accounts Content Manager

    &nbsp &nbsp &nbsp &nbsp &nbsp Accounts My Reports

    Where I want it to show :

    User &nbsp &nbsp Folder &nbsp &nbsp Role

    JBloggs Accounts &nbsp Report Builder, Content Manager, My Reports

    &nbsp &nbsp &nbsp &nbsp &nbsp Production&nbsp Report Builder, Content Manager

  • February 7, 2011 at 9:24 am

    #1283218

    Look at Pivot or search for crosstab queries. That's what you are trying to do.

    Note that these aren't very efficient, so as long as this isn't a large set of data, it works well. Otherwise, it can be a problem.

    Follow me on Twitter: http://www.twitter.com/way0utwest
    Forum Etiquette: How to post data/code on a forum to get the best help
    My Blog: www.voiceofthedba.com

  • February 9, 2011 at 2:05 am

    #1284267

    Thanks

    There isnt that much data, put Im not sure how to do Crosstab queries.

    Ive had a google on how to create them but they are all for summing number values rather than text values.

    Any advice on how I would write this??

    Thanks

    R

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply