I have a request from a dev team to create a proxy account for SSRS.
Their stated goal is to reduce administrative burden.
They want reports to use a data source with embedded credentials. The account would have permissions on the databases in question to access the data/procedures to fulfill the reports.
I can agree with that. It's easier then ensuring each user has the correct permissions on every table.
They also want this account to be included in the DBO role for every user database. At this point I get nervous.
Is this a standard practice? How are others dealing with this scenario?
Bless it or counter it?
asked Dec 16 '09 at 01:56 PM in Default
You have a few possibilities
1) the proxy account mentioned - the easiest way and user do not need to enter a password to da datasource when he runs an report
2) Asking for a password to the datasource when user runs a report.
3) Using user domain acount and database security set for his own account. - hereare three scenarios
If you have no other solution than the proxy account, the I suggest to create a standalone role for this account and Create also separate Reporting schema. Then in this schema create stored procedures as sources for the reports and grant the role only access to the Reporting schema to execute the reporting stored procs.
answered Dec 16 '09 at 04:20 PM
No dbo. You should create a database role, ReportRunners or something, that yougrant specific rights needed to run reports. Should only have Select rights. DBO has rights to add users, objects, etc.. and bypasses security checks within the database. DBO is to the database what sysadmin is to the server.
Pavel provided some good options and 3 is the best one. How much work is it for the network admins to create a group in AD for reporting and then you can add that group to the server and put that AD group in the ReportRunners role in the database. Then all they need to do is add/remove users from the role. They are likely already doing something similar to grant the users rights to get to the Report Server anyway.
answered Dec 17 '09 at 06:04 PM