January 18, 2009 at 10:07 pm
I'm new to managing an IT Department that includes data analysts and database/application programmers, but I'm determined to wipe the learning curve clean 🙂
Question:
Who should have the rights to use the Enterprise Manager (EM) application that is available for use with SQL 2000 Server?
Background (if needed):
With any small organization, some find themselves in dual roles, thus causing a problem when existing employees believe they can maintain a certain level of rights to applications, even after migrating to a new position in the organization. Experience in IT tells me to make them pack it in, but I wanted the word from experts and czars here to set me straight.
I've got a Data Analyst that is persistent in her need of EM and is giving me a good run at it. That said, my word is final, but I want to make sure it is an educated one at best. I have a DBA, so I'm thinking that any work needed thru the EM can be done by him alone, and any information that needs passed onto the Analyst should go thru him first.
I need to keep the access and data secure, even from within the company. While the roles are assigned properly, I can't see why anyone but a DBA would need to use EM.
Thank you!
January 19, 2009 at 12:31 am
Hello,
You mention that you work for a small organisation, but do you have separate Development, Testing and Production environments?
It would probably be useful for the Data Analyst to have extensive right in the Development and possibly the Testing environments, but then restrict “update” rights in the Live environment to the DBA.
In this case you would install EM for your Data Analyst (and other developers), but the DBA would then give them only limited access to the Live DBs e.g. add to the db_datareader and db_denydatawriter roles.
Regards,
John Marsh
www.sql.lu
SQL Server Luxembourg User Group
January 19, 2009 at 10:34 am
John,
Thank you for your reply, and you bring up some valid points that I'm still trying to nail down the specifics on at this time. Our Development, Testing, and Production environments are for the most part seperate; however, I've seen these lines blur depending on the project/task. Because of the "many hats" scenarios that occur in small organizations, I've become very reluctant to agree to the use of EM for anyone other than the DBA. In addition, our Data Analysts don't know how to use EM, so there's a learning curve that may help hold them at bay until I can get all permissions settled.
Because there is not a sound structure here, I want to remove all fear until I can get a beter handle on things. Where you're going with your initial response does shed some light on the fact that if done properly, I can allow the use of EM. I need to get a plan in place!
The way I've got the users setup here is Data Analysts only have the db_datareader right and the DBA continues to maintain owner status. I'll add the db_denydatawriter to the Analysts, but I'm not sure about the DBA. Can I get additional information on what DBA's should be setup as in regards to database access/rights?
January 19, 2009 at 11:05 am
Hello again,
“Can I get additional information on what DBA's should be setup as in regards to database access/rights?”
To be honest when working in the DBA role, I have always had full rights on the SQL Server i.e. added as a member of the sysadmin role.
I suspect you will make life very difficult (if not impossible) for your DBAs if you don’t add them to this role.
Regards,
John Marsh
www.sql.lu
SQL Server Luxembourg User Group
January 19, 2009 at 11:13 am
I completely agree and comply with the rights you state in the DBA's role. If anything, I knew you'd be able to tell me beyond what very little I knew. Your clarification was excellent.
Hey, thanks for all your help on the matter as it has given me the option to allow for more flexibility if and when the staff needs it.
Many Thanks!
January 20, 2009 at 5:47 am
I will add that I think it is good practice for DBA ID's on production servers to be different than their everydya LAN ID's. This means they have to use some sort of remote desktop connection or Citrix to do thier jobs, but it helps prevent situations where someone inadvertently tests a script on a production database.
If the permissions are granted correctly, to the Data Analyst, having access to EM shouldn't hurt. I have read access to the production servers for the apps I support, and various levels on development and acceptance servers. If the data analyst is going to need access to data on a production server in real time, EM is more effective than anything else.
January 20, 2009 at 7:14 am
Ross,
Thanks for the added information as I'm always interested in good and best practices with everything IT.
After conversing with all of you on this, I feel much better in allowing the analysts access to EM. What I need to do next is verify everyone involved is using it along with the proper permissions.
Thanks for your insight!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply