Revoking access from developers acting as DBA''s! help please

  • Hi All,

    I have just been handed over 2 Production SQL servers that we being handled by the SQL developers before. Now they are all cranky that they can't run their jobs and/or edit their DTS's.

    So, I'm going to create a new SQL authentication account that should be used just to update/create the DTS's, and then make it the owner of all the DTS jobs on the servers. BTW, what permissions should I give to this account?

    Also, I'm feeling a lot of hostility that they can't do these things on their own any more. Has anyone else been in a situation like this before? What would you do?

    Thanks,

    TK

  • You'll want to grant EXECUTE permission to the DTS and Job stored procedures in msdb.  The ones I can think of off the top of my head are

    sp_add_dtspackage

    sp_drop_dtspackage

    sp_enum_dtspackages

    sp_start_job

    Greg

  • Regarding cranky developers: You didn't make the permission changes without backing from your management, did you?  When I've been in this situation, it's been in response to a clearly documented management policy.

    Greg

    Greg

  • You do development on your production machines?

  • Ideally developers should do development on the Dev Server and send documentation on migrating DTS from Dev to Prod(which is done by some one else).

    Thanks

    Sreejith

  • I would never change anyone's access without request from management. The only problem was that these guys have been running everything under the sa user. So, when the new server came up, I set it up with a different password, and told them to change anything they had to use a different account or else it wouldn't run.

    They knew this was coming. Also, the interesting thing is, yesterday I found out that my manager wants me to revoke all access from them, and give read only access to everything.

    Now that's going to be really interesting, isn't it?

  • "You do development on your production machines?"

    No, they don't do development on production machines, they only troubleshoot in the production environment.

  • Tell them thanks, but you will grant them access if you need their help.

    They can always apply for a job in production support if they want - offer to pass their CVs to your boss.

Viewing 8 posts - 1 through 7 (of 7 total)

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