Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Duplicating Permissions


Duplicating Permissions

Author
Message
richardmgreen1
richardmgreen1
Right there with Babe
Right there with Babe (784 reputation)Right there with Babe (784 reputation)Right there with Babe (784 reputation)Right there with Babe (784 reputation)Right there with Babe (784 reputation)Right there with Babe (784 reputation)Right there with Babe (784 reputation)Right there with Babe (784 reputation)

Group: General Forum Members
Points: 784 Visits: 892
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?
EricEyster
EricEyster
SSC Veteran
SSC Veteran (296 reputation)SSC Veteran (296 reputation)SSC Veteran (296 reputation)SSC Veteran (296 reputation)SSC Veteran (296 reputation)SSC Veteran (296 reputation)SSC Veteran (296 reputation)SSC Veteran (296 reputation)

Group: General Forum Members
Points: 296 Visits: 520
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.
richardmgreen1
richardmgreen1
Right there with Babe
Right there with Babe (784 reputation)Right there with Babe (784 reputation)Right there with Babe (784 reputation)Right there with Babe (784 reputation)Right there with Babe (784 reputation)Right there with Babe (784 reputation)Right there with Babe (784 reputation)Right there with Babe (784 reputation)

Group: General Forum Members
Points: 784 Visits: 892
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?
EricEyster
EricEyster
SSC Veteran
SSC Veteran (296 reputation)SSC Veteran (296 reputation)SSC Veteran (296 reputation)SSC Veteran (296 reputation)SSC Veteran (296 reputation)SSC Veteran (296 reputation)SSC Veteran (296 reputation)SSC Veteran (296 reputation)

Group: General Forum Members
Points: 296 Visits: 520
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.
richardmgreen1
richardmgreen1
Right there with Babe
Right there with Babe (784 reputation)Right there with Babe (784 reputation)Right there with Babe (784 reputation)Right there with Babe (784 reputation)Right there with Babe (784 reputation)Right there with Babe (784 reputation)Right there with Babe (784 reputation)Right there with Babe (784 reputation)

Group: General Forum Members
Points: 784 Visits: 892
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.
EricEyster
EricEyster
SSC Veteran
SSC Veteran (296 reputation)SSC Veteran (296 reputation)SSC Veteran (296 reputation)SSC Veteran (296 reputation)SSC Veteran (296 reputation)SSC Veteran (296 reputation)SSC Veteran (296 reputation)SSC Veteran (296 reputation)

Group: General Forum Members
Points: 296 Visits: 520
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
stormsentinelcammy
stormsentinelcammy
SSC-Enthusiastic
SSC-Enthusiastic (123 reputation)SSC-Enthusiastic (123 reputation)SSC-Enthusiastic (123 reputation)SSC-Enthusiastic (123 reputation)SSC-Enthusiastic (123 reputation)SSC-Enthusiastic (123 reputation)SSC-Enthusiastic (123 reputation)SSC-Enthusiastic (123 reputation)

Group: General Forum Members
Points: 123 Visits: 806
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...
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search