question

SQLServerMonkey avatar image
SQLServerMonkey asked

SSRS Migration - User Permissions Bulk change

Hello ***Environment:*** **SQL Server Version:** 2008 **Edition:** Standard **Patch Level:** SP3 CU6 (10.0.5788.0) **OS:** Windows Server 2008R2 (x64) **Overview** I have just performed a migration of reporting services between two servers. I used the methodology of backing up the databases (ReportServer & ReportServerTempDB) and restoring them to the new server, then starting and re-configuring the web service. While functionally this is fine it has had the side effect of renaming all of the users within the SSRS environment. It appears that when the web service started up it performed a lookup against AD on the SID for the user accounts and renamed as appropriate, this has resulted in a numbe rof duplicates where once we had Domain1\\User1, Domain2\\User1 we now have Domain1\\User1, Domain1\\User1. The only thing that I can think of that caused this is that we performed a domain migration last year and used ADMT which copied the SIDS. Now I am faced with clearing up the permissions structure, rather than go through one user at a time I am looking for advice on how to perform bulk updates to the configuration. Does anyone have any experience of making this sort of change or any advice on how to alter things en-mass in SSRS? Any advice is much appreciated. John
sql-server-2008ssrspermissions
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

ThomasRushton avatar image ThomasRushton ♦♦ commented ·
@SQLServerMonkey - did you ever get an answer to this problem?
0 Likes 0 ·

1 Answer

·
tanglesoft avatar image
tanglesoft answered
If you look in the users table of the ReportServer do have duplicate rows for the same SIDS. It maybe possible to de-dupe using T-SQL but the structure is a bit of a mess. There are links between polices and user and XML fields in SecData relating back to user SIDS. Reporting Server accesses the tables via stored procedures but there is very little documentation for them. I am just starting to look at the report server web api and it looks like there maybe possibilities using the authorisation methods listed here http://msdn.microsoft.com/en-us/library/ms153661.aspx. I will continue looking and post again.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.