how to block developers to exec stored procedure to update accounting data from back end?

Question

how to block developers to exec stored procedure to update accounting data from back end?

Jinwen Tang

Newbie

Points: 3
More actions
March 16, 2012 at 11:41 am

#254780

I have question:
How do I prevent developers to change accounting data through existing stored procedure which I have already given permission to alter/exec. the stored procedure orignal code is nothing to do with accounting data, but if developers to alter the sp temperary change to code to update accounting data, they can. only solution I have is to seperate alter permission and Exec permission. developers could only have one permission. but we still want developers can access production server to their job on other tables and sps.
thank you!

Viewing 6 posts - 1 through 5 (of 5 total)

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

ouch.

so developers edit a procedure to manipulate data they are not supposed to touch, then put the original procedure body back to it's "normal" definition?

AND this is happening on Production?

wow.

my recommendations:

1. immediately go to management. NO ONE may change data on production, ever, without several steps of approval for fixing issues and bugs. management must have your back to help you stop this dead in it's tracks.

-edit:

1a. Restore a backup to another database and compare the accounting data, and identify what was changed. Produce that report to management, and have THEM track down why developers changed the data.

2. developers should not have access to production. ever. copies of production on a dev machine are fine.

3. remove all their rights from production. period. they need changes? they can give you the tested, approved QA scripts for you to run on their behalf, once it gets approved.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 2

I don’t think that developers should have the permissions in the production environment. They should have permissions on the dev environment, but there is no reason to give them permissions on production database.

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

rraja Default port Points: 1486 More actions · Answer 3

Create a windows group for developer and that group to your production server. give the required permission on that group.

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 4

I'm adding on to what has already been said.

The key here is they have the ability to alter the stored procedure. In production, no developer should ever have the ability to alter any database structure unless there is no other choice. I understand there are exceptions, but these are just that: exceptions. The general rule is only DBAs have the ability to modify database structures in production.

Is that not doable?

K. Brian Kelley
@kbriankelley

Jinwen Tang Newbie Points: 3 More actions · Answer 5

thank you All!!

I really appreciate your responses.