March 11, 2009 at 8:57 am
I am the SQL dba for our company. We have an MI department that is tasked with creating reports etc for users of various applications. They are using reporting services to do this.
I have just had a look at their test server to see what they are doing. They a main database from an application. Then they have created 20 database underneath this, each with views back to the main database. They have based one report on each derived database.
Also they have created an individual login to each database to control user access from the deployed 'portal'. Each login has dbo rights to the relevant derived database.
Before I ask what the hell they are doing a couple of questions (as I don`t really know Reporting Services).
1. Why would you create a database for each report. Surely a single database with views contained within would be much more sensible.
2. Why, when we use AD, would you want to create individual SQL logins for each database, and why on earth would you need to have dbo rights ?
Anyone offer any suggestions please ?
March 11, 2009 at 3:22 pm
(1. Why would you create a database for each report. Surely a single database with views contained within would be much more sensible.)
Good design will include the base tables and views in one database to run all reports as needed.
(2. Why, when we use AD, would you want to create individual SQL logins for each database, and why on earth would you need to have dbo rights ?)
If all the 20 databases are in one instance then AD could do and the DBO account is usually not for users but to create a Shared Datasource which is the account used to create and run the Stored Procedures because limited account may not have the permissions to do what is need to run SSRS. I have covered the permissions issue in the thread below post again if you still need help.
http://www.sqlservercentral.com/Forums/Topic671885-162-1.aspx
Kind regards,
Gift Peddie
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply