Developer Permissions -vs- SQL Server Managability

From: DBADave (anonymous_at_discussions.microsoft.com)
Date: 06/07/04

  • Next message: Greg Frazer: "Application Roles with IIS" 
    Date: Sun, 6 Jun 2004 19:11:02 -0700

    I am faced with a situation where a group of web developers, with basic t-sql skills, periodically develop/modify database objects on a development SQL server that the DBA team manages. The developers insist on having the ability to create/change database objects at will, but also want to be able to test database access via their web-app front-end withoiut having to ask the DBAs to switch DB object ownership from the individual developers that created the objects, to DBO, and without having to specify permissions. Having been a DBA for years, I see the obscurity of this requests and the reprecussions of it, but due to circumstances beyond my control, I need to identify a process that will help them develop and test, but also want to maintain a secure environment where I can track DB changes.

    Below are two proposed solutions. I am interested in hearing from seasoned DBAs that have encountered this situation and am interested in hearing which of the below two choices you would choose - or what other process you used to solve this dilemma in your environment:

    Option1
    - All Developers login to the dev sql server with a generic sql account (ex: DevTest)
    - The DevTest account has dbo rights to the respective development databases
    - While in the 'development' stage, the web developers have the web-app front-end connect to ths DB via the same devtest UserID
    - The above would allow dev to create/modify objects and execute/reference objects without object ownership chaining issues
    - Once this DB goes from the Dev to QA phase, the DBA team changes all object ownership to DBO and sets the DevTest UserID to db_datareader rights only

    Option2
    - The Dev team is granted sysadmin rights to the Dev SQL box, granting them the ability to create objects as DBO
    - The above would allow dev to create/modify objects and execute/reference objects without object ownership chaining issues
    - Once this DB goes from the Dev to QA phase, the DBA team restricts sysadmin rights and permits the Dev team to have the db_datareader rights only

    Of the 2 options, Option2 makes my stomach cringe, but I am interested in hearing which of the 2 options you use, or if there is a better process or policy that you use that allows developers to work somewhat autonomously, while still giving the DBA the ability to manage the SQL server itself.

    Thanks in advance for your assistance!

  • Next message: Greg Frazer: "Application Roles with IIS"

    Relevant Pages

    • RE: How do I load small "wav" files?
      ... Microsoft.DirectX.DirectSound.Device dev = new Device; ... This posting is provided "AS IS" with no warranties, and confers no rights. ... Please reply to newsgroups only. ...
      (microsoft.public.win32.programmer.directx.audio)
    • Re: Developer Permissions -vs- SQL Server Managability
      ... source control and we use those scripts to promote objects to QA and beyond. ... Developers are given access to non-dev boxes only for troubleshooting. ... SQL server that the DBA team manages. ... > - The above would allow dev to create/modify objects and execute/reference ...
      (microsoft.public.sqlserver.security)
    • Re: How to best prepare for asp.net 2.0 release in advance?
      ... Which is best for developers looking to install multiple copies of ... WinXP for dev? ... > additional Operating System is allocated a portion of your existing RAM, ...
      (microsoft.public.dotnet.framework.aspnet)
    • Child domain public folder issues
      ... The Exchange server is in the company.local domain, ... The developers log in to the Dev.Compnay.local ... in the compnay.local domain which there Dev accounts have permisson to the ...
      (microsoft.public.exchange.admin)
    • Re: Developer Permissions -vs- SQL Server Managability
      ... We do NOT want to use a 'fake' user for the developers because we would then ... making them dbo, they won't be able to create them with dbo as the owner. ... SQL server that the DBA team manages. ... > - All Developers login to the dev sql server with a generic sql account ...
      (microsoft.public.sqlserver.security)