March 3, 2009 at 4:40 pm
Hello all. I have a question related to add users to SSRS. I am using SQL Server 2005. Currently I have to connect to the Reporting Services engine and right click on Home then select properties. Then I can add a user (windows account) and grant permissions.
I am looking for a quicker way to do this. I am thinking there has to be some script out there that, given an input of a login id, will add that user with pre-determined permissions to the Report Server. I have poked around the ReportServer database but have not been able to figure it out yet.
Has anyone ever done something like this?
I have automated the creation of logins and usernames for multiple databases to help save time there...now if I can get the SSRS piece automated I will be good to go.
Thanks,
Thomas
March 4, 2009 at 7:38 am
I don't think you can do this the way you are thinking because the user also needs rights in IIS to connect to the web page.
I typically have manged report server rights using AD groups. Accounts Payable group has rights to the Accounts Payable reports, etc...
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
March 4, 2009 at 7:56 am
When I create users manually I don't do anything with IIS. We have it set up to where all users can see all reports. I believe IIS just lets anyone on our domain in.
There has to be some query out there (or at least the knowledge and I can write my own query) that inserts new rows into the correct tables in the ReportServer database to "simulate" what happens when I manually add a user via the GUI.
Thomas
March 4, 2009 at 8:30 am
thomas_w_rawley (3/4/2009)
When I create users manually I don't do anything with IIS. We have it set up to where all users can see all reports. I believe IIS just lets anyone on our domain in.There has to be some query out there (or at least the knowledge and I can write my own query) that inserts new rows into the correct tables in the ReportServer database to "simulate" what happens when I manually add a user via the GUI.
Thomas
Okay, that may be true. I just ran a Profiler trace while I added a login to the ReportServer and the procedure that looks like it is doing the creation is SetPolicy.
Here's the order I see procedures being called (you can and should verify by tracing your own report server):
exec GetSystemPolicy @AuthType=1
exec GetRoles @RoleFlags=0
exec GetPolicy @ItemName=N'',@AuthType=1
exec ObjectExists @Path=N'',@AuthType=1
exec ReadRoleProperties @RoleName=N'Content Manager'
exec ReadRoleProperties @RoleName=N'Browser'
-- this group has some "sensitive" info in the parameters so I'm not including them.
exec SetPolicy
exec UpdatePolicyPrincipal
exec UpdatePolicyPrincipal - yes it was called twice
exec GetSystemPolicy @AuthType=1
exec GetAllConfigurationInfo
exec ObjectExists @Path=N'',@AuthType=1
exec ObjectExists @Path=N'',@AuthType=1
exec GetAllProperties @Path=N'',@AuthType=1
exec GetSystemPolicy @AuthType=1
exec GetPolicy @ItemName=N'',@AuthType=1
exec GetSystemPolicy @AuthType=1
exec GetAllConfigurationInfo
I don't think all of them are needed to add a user, I'm sure many are used just to setup the display and give the option. I also used the web-based ReportManager interface, but I would think that SSMS would be similar.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
March 4, 2009 at 8:37 am
Thanks Jack. Good idea to run the profiler. I will dive into this.
Thomas
March 4, 2009 at 8:57 am
No problem.
Profiler is one of my favorite tools. I don't know what I'd do without it:)
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
June 27, 2012 at 9:46 am
I am trying to do this exact thing. Adding users via T-SQL.
Has anyone out there got any scripts for this?
thanks
Paul
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply