| Author |
Topic |
|
tbrothers
Yak Posting Veteran
83 Posts |
 Posted - 2008-07-16 : 15:21:17
|
| Hi:I have just setup a SQL 2005 Reports Server (seperate from our production database server) and am going to create a shared data source. I will store the credentials with the data source.On the database server I will create a SQL Login named RSUser to be user for the data source credentials. The credentials will be hidden from users and I will be the only person with the password.My question is this. What database permissions are required for this user in order to successfully process reports? If I add this user to the database role "db_datareader" will that be sufficient? Does this grant too broad of a permission range?Thanks,Terry |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-07-16 : 15:26:10
|
| It depends what your reports are doing. Our reports use stored procedures only and they are all prefixed with rsp_, so we just grant exec on all rsp_ objects.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
 |
|
|
tbrothers
Yak Posting Veteran
83 Posts |
Posted - 2008-07-16 : 16:20:53
|
| Thanks Tara.Do you mind if I ask a couple of questions ... so I can learn something?Our reports will use the select statement and also stored procedures.Thanks,Terry |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-07-16 : 16:37:23
|
| For security reasons, we typically grant the reports only the permissions that it needs. So if it needs to select from say 10 tables and there are say 100 tables in the database, we'd rather grant select to those 10 tables than use the db_datareader role. But if you aren't concerned about security as much as we are, then db_datareader would work well for read access. You'll still need to grant permissions to the stored procedures though.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-07-16 : 20:00:12
|
| For permission to access reports you need to give user permission from Report manager or from object explorer. You can include user in browser permission. |
|
|
|
tbrothers
Yak Posting Veteran
83 Posts |
Posted - 2008-07-17 : 12:22:13
|
| Thanks Tara.We use Microsoft Great Plains Dynamics and have 1500 tables in one database alone.Is there a database role with execute permissions, similar to the db_datareader role that grants the ability to run select statements?Thanks sodeep. I've got the report user permissions down, it's the data source login where I am in need. Before my time ... the data source user was added as a dbo. Though I'm not exactly sure how to assign the appropriate permissions (yet) I know it does not need and should not be a dbo.One more question. Our report writers use Visual Studio Pro 2005. When they publish a report, where is it physically published to? Is the report information stored in the ReportServer database? Is there a way that I can view the report code from within SSMS?I'm in the process of reading several SQL books so hopefully I won't have to ask to many questions.Thanks,Terry |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-07-17 : 12:25:54
|
quote:
Originally posted by tbrothers
Thanks Tara.We use Microsoft Great Plains Dynamics and have a thousand tables.Is there a database role with execute permissions, similar to the db_datareader role granting the ability to run select statements?Thanks sodeep. I've got the report user permissions down, it's the data source login where I am in need. Before my time ... the data source user was added as a dbo. Though I'm not exactly sure how to assign the appropriate permissions (yet) I know it does not and should not be a dbo.One more question. Our report writers use Visual Studio Pro 2005. When they publish a report, where is it physically published to? Is the report information stored in the ReportServer database? Is there a way that I can view the report code from within SSMS?I'm in the process of reading several SQL books so hopefully I won't have to ask to many questions.Thanks,Terry
Yes in Report Server database. Look at execution log and other table. |
|
|
|
tbrothers
Yak Posting Veteran
83 Posts |
Posted - 2008-07-17 : 15:52:50
|
| Thanks All.I've created a database named RPT on both the development server and the production server. I've instructed our report writers to save all views and stored procedures to this database. I will grant our data source login (SRSuser) execute permissions to all stored procedures in this database. It is already a member or the database role db_datareader.This should work for us because we are just beginning the process of migrating from Crystal Reports v8.5 to SRS.Thanks,Terry |
|
|
|
|
Data Source Credentials