| Author |
Topic |
|
DBADave
Constraint Violating Yak Guru
366 Posts |
 Posted - 2005-02-25 : 16:37:13
|
| Is it possible to script a stored procedure or trigger through Enterprise Manager and have it NOT include the SET ANSI options?Dave |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-02-25 : 17:00:15
|
| No as that is part of the code in syscomments. But does it hurt anything to have it in there?Tara |
 |
|
|
DBADave
Constraint Violating Yak Guru
366 Posts |
Posted - 2005-02-25 : 17:03:27
|
| We're being told to implement a checksum process to ensure stored procedures, triggers and user-defined functions have not been altered once the developers say it is ok to migrate objects from development to model office. All the code they have created over the years, which is stored in PVCS, does not contain the SET ANSI statements so when we take a snapshot of their original code and compare it to what is in the database (scripted out via EM), the checksum process fails due to the missing SET statements. It's all part of a new change control / separation of duties process that can be a bit of a pain.Thanks, Dave |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-02-25 : 17:06:44
|
| You'll need to script the objects in a different way (possibly via syscomments directly or SQL DMO) or have a VBScript that runs after the scripting to remove these.Tara |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-02-26 : 22:28:03
|
| If you want to remove those extraneous bits you might consider using SED (Sequential Editor). You can give it Find&Replace templates (based on Regular Expressions), and a bunch of Filespecs, to chew on.I've used a freebie one very successfully, I'm pretty sure is was from the GNU stable.Kristen |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2005-02-27 : 06:47:18
|
| How about:1. Checking in a version that includes the ANSI options; (good)2. Requiring the developers to include the ANSI options in their scripts; (better)3. Don't bother comparing to the EM scripts, just re-create everything from PCVS (best)#3 really is the best way, it completely eliminates the problem of whether the code is out of sync or not. Plus it's easy enough to automate a build this way, and if in doubt you could just run the build again. |
|
|
|
DBADave
Constraint Violating Yak Guru
366 Posts |
Posted - 2005-02-27 : 19:12:43
|
| 3 won't work because from an audit perspective it does not guarantee the code the users were testing is the same code contained in PVCS. We need to tie back the code in model office (test) to the code the developers gave the DBAs.Dave |
|
|
|
DBADave
Constraint Violating Yak Guru
366 Posts |
Posted - 2005-02-27 : 19:37:58
|
| 1 & 2 has been discussed with the development managers and will probably be a requirement. Dave |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2005-02-27 : 23:48:31
|
| The only reason #3 won't work is because you're not requiring all code to be checked in, and making your source control your ONLY source for code. Anything done outside of source control does not exist, for all intents and purposes. Making PCVS the standard simplifies all aspects of deployment. If developers are not checking in all code, and testers are not testing this code, there's no way to know exactly what works and what doesn't, regardless of what other code you compare it to. |
|
|
|
DBADave
Constraint Violating Yak Guru
366 Posts |
Posted - 2005-02-28 : 13:17:58
|
| I'm not sure I understand. The development area does require all code to be checked into PVCS. However there is currently nothing in place to prevent anyone with SA or db_owner permission from modifying the code prior to migration from one environment to another. We're relying on the honor system that people are following the checkin/checkout procedures. The auditors want us to prove to them the code in PVCS matches the code that exists in the datbases at the time of user sign-off.There are migration tools we can purchase to prove this point, similar to tools that exist in the mainframe world, however management does not wish to spend the money at this time. Instead they wish to implement a process that is better then we have now, but will not protect us 100% from malicious activity. Dave |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2005-02-28 : 13:56:41
|
| My point was, if you ALWAYS build from PCVS, then it does not matter if they change it outside source control, you'd simply overwrite it with the current version. You wouldn't migrate from development to test/staging or to production, EVERY server would be built ONLY from source control.I'm not saying there's no way to audit it, but auditing will only tell you it was changed, not who did it or why, and it does not solve the problem of unauthorized changes. If there's nothing in place to prevent this, the first step should be to implement something that does. If you're concerned that someone is bypassing this process, you need to address the person doing it. |
|
|
|
DBADave
Constraint Violating Yak Guru
366 Posts |
Posted - 2005-02-28 : 14:30:51
|
| I undertstand, but unfortunately that will not satisfy our auditors. The issue audit has with that approach is we're still not proving that what the users have been testing with in model office matches the code in PVCS. And yes we can reprimand the people who don't follow the rules, but that won't prevent bad code being migrated to production. It's not my rules, but audit's and since we're a financial institution we must comply with their requirements.That being said I still can't think of a legitimate situation where migrating code from PVCS and not comparing it to what the users were testing would be a problem. Outside of careless checkin/checkout, which I wouldn't know how to stop, what could go wrong. Is audit trying to tell me that a developer might ask the DBA to migrate some code from development to model office and that it's possible a DBA may notice a flaw in the code, fix the flaw, not tell the developer, users approve the code and then the original flawed code gets migrated to production. It's a bit of a reach, but I must do what I am told.Thanks, Dave |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2005-02-28 : 20:56:56
|
| I'd really like to help, but I can't provide any advice other than what I've already done. I apologize if my responses were not clear, frankly I'm too aggravated at your !@#$!@#% moron auditors to make a lot of sense. I CAN tell you that what I've described WORKS, nearly all professional development houses follow these guidelines, and it produces solid, well-tested code. Your auditors are the only people preventing you from implementing it. |
|
|
|
DBADave
Constraint Violating Yak Guru
366 Posts |
Posted - 2005-02-28 : 23:00:53
|
| I appreciate the help. I have nothing against auditors. There are some good ones out there, unfortunately most of the ones I've encountered are not too technical and usually concentrate on the wrong areas. For instance, we have a number of batch jobs with hard coded ids and passwords. They never ask about them. These same auditors thought we had too many ids with sysadmin permission and wondered why SA couldn't be removed. The other ids were the service account, a domain group belonging to the DBAs and distributor_admin. Go figure.Thanks, Dave |
|
|
|
|
Scripting Objects via Enterprise Manager