Re: Developer Permissions -vs- SQL Server Managability
From: michelle (michelle_at_nospam.com)
Date: 06/07/04
- Next message: Sonal Chauhan: "Error 15023: User already exists in current database"
- Previous message: Rand Boyd [MSFT]: "RE: Application Roles with IIS"
- In reply to: DBADave: "Developer Permissions -vs- SQL Server Managability"
- Next in thread: Dan Guzman: "Re: Developer Permissions -vs- SQL Server Managability"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]
Date: Mon, 7 Jun 2004 12:58:06 -0500
We're working on implementing something similar right now and are still in
the stomach-cringing phase.
We do NOT want to use a 'fake' user for the developers because we would then
lose the ability to audit exactly who did what. Rather than sa, though, we
are assigning the developers to the db_owner role of 'their' respective
databases. Fortunately ours are not *supposed* to make schema changes (just
procs) but we have come to rely on all procs being owned by dbo and without
making them dbo, they won't be able to create them with dbo as the owner.
The developers will deploy their own procs to production. Basically the DBA
team is now responsible for admin, schema, and standards only.
We've still got a few glitches to work out like jobs/dts packages.
"DBADave" <anonymous@discussions.microsoft.com> wrote in message
news:B799B132-855A-4D21-934A-FF1030D82964@microsoft.com...
> 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: Sonal Chauhan: "Error 15023: User already exists in current database"
- Previous message: Rand Boyd [MSFT]: "RE: Application Roles with IIS"
- In reply to: DBADave: "Developer Permissions -vs- SQL Server Managability"
- Next in thread: Dan Guzman: "Re: Developer Permissions -vs- SQL Server Managability"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]
Relevant Pages
|
