October 3, 2007 at 5:34 am
Hi, hope someone can help with this issue which perhaps someone else has hit.
We have a production server supporting SQL 2005 database engine, analysis services and reporting services. There is a production DBA who knows the database engine but is not experienced in supporting AS and RS (me), plus a team of development DBAs who know AS and RS but should not in my opinion be let loose on the database engine in production (because I then have responsibility but no control).
Is it possible to set up access so dev dbas can fully administer AS and RS but do not get full sysadmin to database engine?
cheers
george
---------------------------------------------------------------------
October 3, 2007 at 6:28 am
That will depend on what everyone calls administration in your groups.
If they have to be able to start and stop services, r use terminal services in administration mode, they will need to be local administrators. Now you can remove the local administrators from the SysAdmin role on the SQL server, but it can turn into a lot of work.
If they don't need access to the services and they are not set up as local administrators on the server, The logins for those that need RS administration permissions do not need much and AS administrators need even less. However, when you get into job scheduling for SSIS and RS, you are back to a group that needs some additional access.
I know it is difficult to give people control over something you are responsible for, but I think you are going to need to get that kind of trust built between yourself and the others you are working with on this. On the bright side, they are going to need the same kind of trust with you because it will be equally difficult to prevent you from deleting a report job from the job agent or an SSIS package from the MSDB database.
October 3, 2007 at 8:02 am
Michael,
---------------------------------------------------------------------
October 3, 2007 at 8:22 am
er.....not sure what happened there........
Michael,
thanks for that.
DevDBAs will not need to stop/start services and local admin on the server will definitely be out.
Can you expand on the permissoins needed to administer SSAS, RS and their SSIS packages
thanks
george
---------------------------------------------------------------------
October 3, 2007 at 11:08 am
Ok, if they do not need to be admin's, then you have a chance...
Reporting Services - if they are only going to need to be able to add folders and reports, add a local user group to the server that reporting services in running on and add the users to this group. Then, you can set permissions using Report Manager to allow these users full permisions within Reporting Services. They should just need appropriate access to the databases the reports run against.
SSAS - Analysis Services adds a few groups to the local system when you install it. The are reasonably self-explanatory. Add the users to the correct groups and give them whatever access to the datamart databases that you think is appropriate (they may need to add tables, views, and procedures).
SSIS is a bit more tricky, it uses DCOM.
Here are a couple of links:
http://sqljunkies.com/WebLog/knight_reign/archive/2006/01/05/17769.aspx
http://mohansmindstorms.spaces.live.com/Blog/cns!69AE1BEA50F1D0E7!213.entry
If you search these forums, you will find much more in-depth explanations of how to tightly secure each of these products individually.
October 4, 2007 at 2:06 am
thanks michael, I'll give it a go and let you know how it pans out. Might be a while as I have to go through the management procedures and get agrement from all parties.
cheers
george
---------------------------------------------------------------------
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply