| Author |
Topic |
|
rwlopez
Yak Posting Veteran
80 Posts |
 Posted - 2006-10-12 : 12:23:29
|
| I am having trouble with the security settings in SQL Server 2005. I have one database that has a series of views that look at several other DB's. I had a problem at first allowing users to view reports using reporting services, I got around this by making all of the users to db_datareaders on each DB. However now that I am using some parameterized reports and reports that are calling upon functions, these settings are no longer working. Does anyone have any suggestions on how to allow the minimum amount of security that will allow my users to view published reports? |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-10-12 : 15:02:04
|
| For scalar functions, you also need to grant EXEC on the UDFs. We use stored procedures for everything, so all we have to do is grant EXEC on those and nothing else.Tara Kizer |
 |
|
|
rwlopez
Yak Posting Veteran
80 Posts |
Posted - 2006-10-12 : 17:19:27
|
| Actually using a stored procedure is not working either. The problem seem to be when I use a query in the report that JOINS two or more views together. If a use a query with one single view and no joins it works fine, any suggestions? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-10-12 : 17:22:49
|
| When using the stored procedure, did you grant the user EXEC on it? If so, what error are you getting?Tara Kizer |
|
|
|
rwlopez
Yak Posting Veteran
80 Posts |
Posted - 2006-10-12 : 17:44:28
|
| An error has occurred during report processing.Query execution failed for data set 'TestDataSet1'.For more information about this error navigate to the report server on the local server machine, or enable remote errors This is the error I get when trying to view the report using the Report Server intranet site with a user who is not a sysadmin. Yes I grated EXEC right on the sp. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-10-12 : 17:46:47
|
| Connect to Query Analyzer or Management Studio (depending on what version of SQL Server that you are using) using the same userid that you are using for the report. Try executing the stored procedure there. Post the error here if any.Tara Kizer |
|
|
|
rwlopez
Yak Posting Veteran
80 Posts |
Posted - 2006-10-12 : 18:49:11
|
| Thank You very much for your help, I needed to add permissions on a couple of more DB's once I did that it worked fine. I do not need to use stored procedures, once I made the changes all of the reports worked properly, however do you recommend using stored procedures for reports rather than having the SQL embedded in the Data Set? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-10-12 : 18:57:17
|
| Yes I do. I recommend stored procedures for all data access. We don't allow any data access except through stored procedures where I work. But there are people who sit on the other side of this fence.Tara Kizer |
|
|
|
|
Security