DBA without system administrator privileges

Question

DBA without system administrator privileges

Harry Swindells-331936

SSC Veteran

Points: 214
More actions
June 14, 2007 at 7:56 am

#196405

Hi All
Here is a general admin question for any DBAs out there who do not have system admin on their database servers. I'm wondering what the impact may be if that privilege was not available for DBAs who are responsible for production database support and how they manage without it, assuming they have database owner privileges on the actual databases.
I know some SQL 2000 features just aren't available without it e.g. profiler. Anybody have experience of supporing live databases services without being system admin?
Regards

Viewing 15 posts - 1 through 15 (of 25 total)

You must be logged in to reply to this topic. Login to reply

Scott Arendt SSCrazy Eights Points: 8218 More actions · Answer 1

Another department in my company that was responsible for the ERP system wanted my help, but did not want to grant me admin access. Their concern was that there was sensitive data contained on that server.

I struggled along for several weeks trying to help them, but it was difficult to do things when you are having a non-developer trying to describe databases, tables, etc instead of being able to jump on the box and look around.

Finally, my supervisor told me to give them an ultimatim. Either grant me access or figure it out on your own. They have never called back (even though I know that they did not accomplish all that they intended ...)

This may not help you with your problem, but it sure solved mine.

Regards, Scott

Harry Swindells-331936 SSC Veteran Points: 214 More actions · Answer 2

Thanks Scott. Any info or real life experience on this topic is useful to me.

Regards, H

Mark Harr SSCrazy Eights Points: 9921 More actions · Answer 3

Their concern was that there was sensitive data contained on that server.

Each and every time that is said to me, my comeback has been "Why do you think I am interested in that data?". Like Scott, I insist on full admin rights to perform my job (as consultant to come in an investigate or fix problems), and inform them the alternative is that I leave without the accomplishing work desired, and I charge them for the full day or contract period anyway (since I reserved my time for them, they have to pay for it). When it came down to that ultimatum, a client has not ever failed to give me the admin rights.

I realize this is not exactly to Harry's original issue, but thought it might help those in similar situations.

Mark

Harry Swindells-331936 SSC Veteran Points: 214 More actions · Answer 4

Thanks Mark.

The server boys are telling my manager SQL DBAs only need database owner privilges on the databases and not SA on SQL Server.

Regards, H

Rudyx - the Doctor SSC-Forever Points: 43695 More actions · Answer 5

I can see, and have had to make due, without LocalAdministrator on a SQL Server before. This is workable but a handicap.

But a DBA without sysadmin ??? No way !!!

RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

Mark Harr SSCrazy Eights Points: 9921 More actions · Answer 6

Harry;

Tell the manager of the "server boys" that they should be able to get their jobs done being "Power Users", if they were any good. Then you can remind them that they know as much about databases as you do about Active Directory.

(I'm willing to bet that they setup the database servers to run all files on Raid5 "for recoverability". Usually it's the same guys.)

Mark

Kenena Klosson SSC Eights! Points: 958 More actions · Answer 7

I have the same situation. My full time job is managing and developing one relatively small asset management database. I query for issues weekly, monthly and quarterly and they don't get involved in my database other than to install it for new users. Things run smoothly. Honestly, I think the IT people are frequently overwhelmed with new technology, researching and testing additional new technology to meet user demand with limited resources to acclimate and limited new hire ability so they have enough trouble maintaining their systems in good order. Probably why they don't what to involve unknowns into their circle as the unknowns can change but the assumed access would not. At least when they screw up, it's their night or Saturday required to fix it. There is a question, though, of not getting the DBA title and pay, as in my case. Hmmmmmmmm.

Harry Swindells-331936 SSC Veteran Points: 214 More actions · Answer 8

Thanks Grasshopper, and thanks to everybody who replied to this post.

Regards, H

Terry Barkoulas SSC-Addicted Points: 437 More actions · Answer 9

As one of the "server boys" I work closely with our DBAs, and if I can get by not giving them administrator access.. I would, but I realize that if their hands are tied (especially when applying hotfixes/service packs)things don't get done

This is why I agree with you guys in that you need to trust your DBAs and give them the necessary rights to do their job. Besides there is something called "auditing" which can track what people with admin rights are doing on the system.

Harry Swindells-331936 SSC Veteran Points: 214 More actions · Answer 10

Thanks Terry. I'm a great believer in trust - coupled with change control and auditing!

Rudyx - the Doctor SSC-Forever Points: 43695 More actions · Answer 11

Agreed.

RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

Steve Jones - SSC Editor SSC Guru Points: 736255 More actions · Answer 12

I'm ok with not having Windows admin privileges IF those that do have them are on call and responsive when I need something done.

I'd be more concerned over having Windows admins with SA rights. They're not necessarily the ones that need to get inside SQL Server, admin it, tune it, etc. I'd think they shouldn't have rights to sensitive data.

If a DBA shouldn't access certain databases for some reason, might be time to get a DBA. Someone has to access the data. Secretaries get access and their tenure might be as flaky as a DBAs. You've got to trust the DBA, get them to sign an NDA, bond them, but trust them.

Patches can be deployed by either, just need to be tested by a DBA that can work with the app and watch for query issues.

Harry Swindells-331936 SSC Veteran Points: 214 More actions · Answer 13

I agree - I have seen some very useful info on here about DBAs stopping Windows Admin users from having SQL SA access. Here Windows Admin users are talking about stopping DBAs from having SQL SA access.

I'm wondering if there are combinations of other privileges which allow DBAs to cover their core responsibilities without having SA. At the moment I'm struggling with backup and recovery, database security and database performance (can't use Profiler) which all seem impossible to manage properly.

Ross McMicken SSCarpal Tunnel Points: 4436 More actions · Answer 14

Our DBA's have admin rights, but not on their primary logins that are used for general LAN access, email etc. All admin ID's are restricted to use on servers. This prevents DBA's from accidentally doing bad things with their regular ID's. They use RPC to login to the servers. On some systems, the admin ID's mus tbe checked out of a firecall system.

Keep in mind that SOX regulations have an impact on who has write access to production data. It's also good form to make sure that all changes to production data are authorized by the owner of the data - you DO have someone in the business who owns the data, don't you?