Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Duplicating Permissions Expand / Collapse
Author
Message
Posted Tuesday, February 25, 2014 1:56 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 4:46 AM
Points: 345, Visits: 417
Hi all

Is there a way (in either SQL or some other method) of getting one users permissions and duplicating them for another user (sort of like a template)?
I've just finished a very basic restruicture of our SSRS setup and need to find a way to duplicate permissions if we get an email saying something along the lines of "please give Person B the access to the same reports as Person A" (I've had a couple already but they were only a couple of reprots each).

I'm hoping there's a way to do this in coode rather than manually.

We don't have access to AD groups so those are out unfortunately.

Anyone any ideas?
Post #1544800
Posted Tuesday, February 25, 2014 6:06 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, May 5, 2014 6:31 AM
Points: 288, Visits: 519
I highly recommend using database roles for this requirement. Assign the permissions to the role and the next time around you just add the user to the role.
Post #1544862
Posted Tuesday, February 25, 2014 6:16 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 4:46 AM
Points: 345, Visits: 417
I hadn't thought of that but aren't they restricted to the basics few in 2008?
Also, things aren't quite as straightforward as they first appear.
The reports are in folders which relate to the type of information in the report.
Certain people have access to all the reports in a given folder, but other people might only have access to a couple.

Would database roles work in this situation?
Post #1544870
Posted Tuesday, February 25, 2014 7:22 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, May 5, 2014 6:31 AM
Points: 288, Visits: 519
my bad, need another cup of coffee

You can copy the records in the DB, but as always, MS does not recommend that approach

In SSRS Roles = permission sets such as content admin, etc


select *
from dbo.PolicyUserRole PUR
join Policies P
on PUR.PolicyID = P.PolicyID
join roles R
on pur.roleid = r.roleid
join users U
on PUR.UserID = U.UserID


If you are familiar with .NET, everything is exposed through the objects and you can build a custom page or VB.Net app to automate some of the chores.
Post #1544898
Posted Tuesday, February 25, 2014 7:53 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 4:46 AM
Points: 345, Visits: 417
MS doesn't recommend a great deal from the googling I've done (including ruinning SELECT statements against the ReportServer database).
I'm not familiar with any of the .NET languages so this might be an opportunity to learn something.

I just wanted an easy way to mirror someone's permissions to another user.
Post #1544933
Posted Tuesday, February 25, 2014 7:58 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, May 5, 2014 6:31 AM
Points: 288, Visits: 519
The standard approach from MS would be to use AD groups to nest the permissions. Example:

new user bob would be added to the accounting group
the accounting group is a member of the SSRS_Account and SSRS_DataWarehouse AD groups
the groups are assigned permissions to reports in SSRS

bob would inherit permssions to the accounting and datawarehouse reports
Post #1544939
Posted Tuesday, February 25, 2014 7:59 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, September 29, 2014 8:20 AM
Points: 120, Visits: 688
EricEyster (2/25/2014)
I highly recommend using database roles for this requirement. Assign the permissions to the role and the next time around you just add the user to the role.


Another suggestion that could be useful is to use impersonation...
Post #1544940
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse