July 22, 2009 at 1:48 pm
We have a small IT department with 2 network positions, 1 SQL Server DBA/Seveloper and 1 Application Developer/Analyst.
Our developer has full access to alll the same tools the DBA does including MS SQL Server Management Studio . Sometimes this is necessary because the developer is the backup for the DBA if they are not available.
We think there should be some segregation of duties but since we are a small department there is a fine line. A further note is our developer has full access to the DB to do whatever they want and the DBA is ultimately responsible for the DB but in some cases the developer has created the tables, etc and they almost always bypass the DBA for all activities currently (not saying it is wrong, just investigating if we need to tighten things up).
Maybe this is acceptable but we would like to know how other small to midsize companies with a small IT department handle segregation of duties between application developers and DBA's. Our developer comes from the MS Access world where you create the front end and back end all in MS Access so we are just switching to SQL Server 2005 as the back end in cases where it makes sense.
Any feedback and/or questions you may have is greatly appreciated.
July 22, 2009 at 2:08 pm
Probably unavoidable in a deparment that small. I'd sit down with the DBA and Developer and make sure that they are working closely together to ensure that both know what the other is doing and why so there are no surprises from either side.
July 22, 2009 at 2:40 pm
I have to agree with Lynn.
Shops that small don't lend themselves to having a controlled job responsibilities. The smaller the team, the more blurred the line becomes. The one thing that is crucial is some form of change management, even as simple as email to make sure that the DBA and the Developer are on the same page. You will want to put tighter controls on production systems. Granted these will tend to be procedural and not physical, but they help.
Good Luck! 😎
July 22, 2009 at 3:58 pm
In such a small shop I would agree with the others, just ensure you use the resources to their strengths.
the only thing that worries me is the phrase 'the DBA is ultimately responsible for the DB but in some cases the developer has created the tables, etc and they almost always bypass the DBA for all activities currently ' - that sounds wrong and must be tightened. Neither of the two resources can be held responsible if they have no control and bypassing them is deemed acceptable.
---------------------------------------------------------------------
July 22, 2009 at 4:58 pm
I agree with the others in that it may be difficult in a shop that small. I also agree with George in that the DBA to developer roles/responsibilities must be tightened. It is one thing for a developer to design and create a table, but another for that table to make it into a finished product without going through a DBA if one is available. I would push for creating database standards that the developer(s) can use as they create schema. Part of the development standards should outline the DBA's role in reviewing developer proposed schema and giving the final thumbs up for allowing the changes (no matter how small) into the product.
Just my opinion, but developers and DBAs have different concerns when it comes to software development. The developer is usually concerned with getting the application to work per the requirements and willing to design schema to 'make it work' whereas the DBA should be more concerned the physical table design, indexing strategies, scalability, performance, integrity, etc. Not allowing the database changes to be approved by the DBA is a mistake in my opinion!
July 22, 2009 at 9:39 pm
There are two basic types of DBA's and they've both been described in this thread. Big corporation or small, these two types of DBA's can and do exist. They are both equally responsible for the integrity of the data. If something goes wrong, kill both of them because they are both at fault. They can and must learn to work together... there are no exceptions. They must both respect the metal and the material. 😉 Add the SysOp in there, as well. He/she must also work very closely with the DBA(s).
Basically, I agree with Lynn but to a much higher level.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 23, 2009 at 8:03 am
I agree with Jeff. In a small shop, you have to cross the lines, a lot, but the fact is, it's your job to work together. That's it. End of story. If anyone involved is being a... well, let's say, being problematic, then that has to be shut down, hard. Even within this, you should be able to specialize to a degree. The Dev/DBA may do a lot more of the development & design work, but the DBA/Dev should be more responsible for ensuring backups, integrity checks, and a final design review. But regardless of how or where you draw the line, you must communicate as Jeff outlined. That's what you're getting paid for, not just your ability in C# or TSQL.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 23, 2009 at 9:03 am
I would like to thank each and everyone who took the time to reply. I believe there are some areas that you pointed out where we need to work more closely together and we can tighten up our current procedures and protocol. Thanks once again!!!
July 23, 2009 at 9:22 am
One small addition. I'd make sure they all use their Windows account, not SA, to make changes. It will help them to be respectful towards each other, and provide a way for the other people to see what might have been done by someone else. People take vacation, get sick, etc., and having an audit control here just provides more information that can help you troubleshoot.
I'd agree they need to work together, and they need to establish strict control rules (script things, source control, etc.) so that each knows what the other does.
I'd also be sure the sysadmin knows how to run SSMS, and can take direction over the phone. Remote hands are nice to have when you're out at dinner with your family and an issue arises.
July 23, 2009 at 12:59 pm
Steve Jones - Editor (7/23/2009)
One small addition. I'd make sure they all use their Windows account, not SA, to make changes. It will help them to be respectful towards each other, and provide a way for the other people to see what might have been done by someone else. People take vacation, get sick, etc., and having an audit control here just provides more information that can help you troubleshoot.I'd agree they need to work together, and they need to establish strict control rules (script things, source control, etc.) so that each knows what the other does.
I'd also be sure the sysadmin knows how to run SSMS, and can take direction over the phone. Remote hands are nice to have when you're out at dinner with your family and an issue arises.
I absolutely agree with that and I'm sorry I forgot to mention it. As distasteful as it sounds, "blame control" is absolutely essential... it keeps folks from trying to be sneaky and, if used properly, can be used to identify "educational opportunities" that might otherwise go unnoticed. It will also keep unnecessary finger pointing and punishment of the innocent to zero.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 23, 2009 at 1:54 pm
While I understand (first hand) the practice of blaming others.
If we as Professionals do not assume the Responsibility of and Accountability for our actions, then we should not be considered Professionals. Let the quality of your work provide you protection from the less secure. I guess what I am saying is do things for the right reason! Audit trails are one of the tools I rely on daily. They are there to clearly identify what has transpired. They should aid us in design of other things like Security and better code that would not allow unusual events to occur. They also provide a window full of opportunities for training of the developers, the DBA and the End Users.
2 cents! No more soap box for me. 😎
July 23, 2009 at 4:59 pm
doug.williams (7/23/2009)
While I understand (first hand) the practice of blaming others.If we as Professionals do not assume the Responsibility of and Accountability for our actions, then we should not be considered Professionals. Let the quality of your work provide you protection from the less secure. I guess what I am saying is do things for the right reason! Audit trails are one of the tools I rely on daily. They are there to clearly identify what has transpired. They should aid us in design of other things like Security and better code that would not allow unusual events to occur. They also provide a window full of opportunities for training of the developers, the DBA and the End Users.
2 cents! No more soap box for me. 😎
You miss my point... "blame control" eliminates such things as "blame"... that's why it's called blame "control". 🙂
--Jeff Moden
Change is inevitable... Change for the better is not.
July 24, 2009 at 12:27 pm
I didn't notice it specifically, so I'll ask it:
Are there separate databases for 1) development, and 2) production?
Or better yet, 1) development, 2) staging/testing, and 3) production?
The code thus belongs to he Developer, but the DB belongs to the DBA (no matter whose schema is eventually adopted).
The Dev might need only read-only access to the production DB, perhaps forsupport/diagnosis, but could be granted full read/write/ddl, or even dbo, to the development DB. The DBA would of course have full SysAdmin privileges to all DBs.
Using the domain user, as was pointed out earlier, is a must unless there's an obscure reason to make an exception. And take that SA login and disable it! Either use Windows authentication only (preferred), or if Mixed-mode is required, change the SA password to the results of "SELECT NEWID()".
We have created a domain group called "SQL Admins", of which I am the only member. The first act I perform on a new installation of SQL is to add the "SQL Admins" group to the server instance and then remove BUILTIN\Administrators. If I then get hit by the proverbial truck or thrown under the proverbial bus, the domain admin can add my temporary or permanent replacement to the group and carry on.
Yeah, separation of duties.
Mike Hinds Lead Database Administrator1st Source BankMCP, MCTS
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply