How to Make a DBA’s Day

If you’re a DBA, you’ve been there.  If you’ve been a DBA for more than…say…1 month, you’re probably becoming numb to it.  Vendors and their database connections. If you’re a DBA, you’ve probably already guessed what I’m talking about.

the setting

The application vendor support guy shows up to install some new software package. Their application needs access to the database server and needs to own the application’s database(s). Fair enough, we can do that. Then they tell you that user also needs to have sysadmin privileges on the server it’s self. Being a good DBA, you’re first response is, “Hell, no!”

But, that’s never worked before and wanting to be a kindler, gentler DBA you decide to come at it from a different point of view with, “What are you doing that you need sysadmin privileges for?”

Unfortunately, you are routinely disappointed with an answer like, “I just know the application won’t work without it.”

And there it is.  You tried to control your rage, but deep down you knew this answer was coming.  Why?  Because you have heard that answer from every other vendor that’s come by your cube, that’s why.  Not once have you had a vendor surprise you with any kind of sufficient answer.  The best you can hope for is that they tell you its only temporary for the install and that the application itself doesn’t need sysadmin privileges to run on a daily basis. They still don’t know why it’s needed for the install, but at least they’ve acknowledged that’s a bit too much power for a runtime user.  Unfortunately, even that answer is in the minority.  And people wonder why DBA’s become bitter old coot’s sitting in the corner mumbling about the next person that asks them for something is going to get shanked with this thumb drive…er…I mean…how can I help you?

listen up, vendors!

So, as a public service I’m going to provide some advice to all those application vendors out there on how to explain your need of system privileges because you don’t need sysadmin privileges for everything you think you do.

1. I need it to create objects in the application database. – No you don’t. If the user already owns the database, it should have no problem creating and modifying the objects in the database itself.  If it doesn’t own the database, it still doesn’t need sysadmin privileges or even db_owner privileges.  You could just grant the db_ddladmin role to the user account and you’re in business.
2. I need them to create a database – No you don’t.  The user can be granted dbcreator privileges. In this case, Microsoft was kind enough to give the role a title that describes exactly what it does.
3. I need them to add logins – No you don’t. The user could be granted securityadmin privileges. How about managing user privileges in the database? Take a look at db_accessadmin.
4. I need them to create and run SSIS packages – No you don’t. The user can be granted the appropriate role(s) in the msdb database: db_ssisadmin, db_ssisltduser and db_ssisoperator.  That must be new, right? Um, well only if you don’t count db_dtsadmin, db_dtsltduser and db_dtsoperator.
5. I need them to do bulk inserts – No you don’t. Have you ever even looked at the server roles? There’s one in there just for you: bulkadmin.
6. I need them to add and modify SQL Agent jobs – No you don’t. Again we can visit the roles in the msdb database and grant you the appropriate level of permissions with the SQLAgentOperatorRole, SQLAgentReaderRole and SQLAgentUserRole.
7. I need them to add new categories for agent jobs – No you…wait, actually you do need sysadmin privileges for that one.  And I recently ran across an install that was doing just that.  So, they actually did need those privileges. I could have added it for them, but it was wrapped up in the install package and the installer wasn’t even sure when it did that or why.

Sigh. Just once I’d love for someone to actually even try to justify it with any of the excuses in my list.  Especially the last one, where the install did indeed require sysadmin privileges to execute properly. But alas, to date that has never happened for me.  Not a single person could ever tell me what they were doing that actually required the sysadmin privileges. I always get the standard, “Well, it never gives us any problem with sysadmin privileges.”

MUST.CONTROL.FIST.OF.RAGE.

at least try, please?

You would make my day by at least trying to come up with an excuse.  Even if it’s not a valid excuse, at least try something.  Please, I beg of you, don’t be a lazy application vendor.  And to what must be an extremely rare minority of vendors who actually do have an excuse, make your name known.  You will be revered by DBA’s around the world, and I promise to put the thumb drive down when you walk towards my cube…er…I mean…how can I help you?

Advertisement