Re: Stored procedure update permission

From: Dan Guzman (guzmanda_at_nospam-online.sbcglobal.net)
Date: 09/11/04 

Date: Sat, 11 Sep 2004 10:09:52 -0500

We keep all DDL scripts under source control. DBAs install these in UAT and
production as part of our change control process. 

-- 
Hope this helps.
Dan Guzman
SQL Server MVP
"Ray Kurpiel" <RayKurpiel@discussions.microsoft.com> wrote in message 
news:8B76606C-1509-4501-BB4F-DB7F62DB81EE@microsoft.com...
> Thanks for the response, Keith. Then, to migrate to test and production, 
> the
> dba  collects the stored procedure changes and migrate to test and 
> prodcution?
>
>
> "Keith Kratochvil" wrote:
>
>> Within our development database:
>> Each developer has their own sql login and each developer has rights to
>> create stored procedures.  The application that we developed is written 
>> so
>> that it will connect to the development database server using their 
>> specific
>> sql login.  When they execute procFoo SQL Server automatically tries to
>> execute InsertDeveloperNameHere.procFoo.  If it does not find procFoo 
>> owned
>> by InsertDeveloperNameHere it will run the dbo version of procFoo.  This
>> allows each developer to work on their own code, make their own changes 
>> to
>> stored procedures, and not impact anyone else.
>>
>> Within our test database (and higher) everything is owned by dbo and a
>> different set of logins is used.
>>
>>
>> -- 
>> Keith
>>
>>
>> "Ray Kurpiel" <RayKurpiel@discussions.microsoft.com> wrote in message
>> news:B6857441-6F6B-4A6F-841A-444CE567392A@microsoft.com...
>> > As a DBA, I've always had an issue with the way updates to stored
>> procedures
>> > are treated the same as changes to table structures in SQL Server. My
>> primary
>> > concern has been with the design of table structures and I generally 
>> > let
>> the
>> > developers make changes to stored procedures in a test database and I
>> migrate
>> > them to production. However, unless you grant the developers db_owner 
>> > or
>> > db_ddladmin to the database, they can't make changes directly to stored
>> > procedures in the public (dbo) schema and I don't want them to have the
>> > ability to change tables. I do grant them the ability to "Create SP" 
>> > but
>> they
>> > can only update their own stored procedures. Subsequently, I must 
>> > change
>> > ownership of these stored procedures to dbo for integrated testing ( I
>> wish
>> > Microsoft would provide a separate fixed role that would allow updates 
>> > to
>> the
>> > public stored procedures). How does everyone else handle this issue? Is
>> this
>> > the only way? Any ideas?
>>
>>

Relevant Pages

  • Re: Dont understand what version of SQL to install
    ... I created a database using sql 2005 express edition. ... With that in mind - could you use SQL Server 2008 Developer Edition? ... installed on Client OSs such as Windows Vista. ...
    (microsoft.public.sqlserver.setup)
  • Re: Serious errors with Create view command
    ... the database is opened? ... There's no database on earth that behaves that way! ... What on EARTH does the option of 'Edit Stored procedures' do? ... I can't paste it INTO the view designer sql view ...
    (microsoft.public.fox.helpwanted)
  • Re: Dont understand what version of SQL to install
    ... but "moving" a database implies a degree of automation. ... Principal SQL Infrastructure Consultant ... To my XP, I can add SQL Developer 2008 to develop (I guess I should say, ... installed on Client OSs such as Windows Vista. ...
    (microsoft.public.sqlserver.setup)
  • Re: Transaction Oriented Architecture (TOA)
    ... OOP is not required to wrap SQL. ... of stored procedures to act as an API for application programmers ... writing code against the database. ... With a well-defined API in place, ...
    (comp.object)
  • Re: Dont understand what version of SQL to install
    ... Subscription and I still have to pay $49 to use SQL Developer? ... I don't think you have to buy another license for the Developer Edition, but you better ask this question to whom you bought it. ... it's not stated that SQL Server 2008 Enterprise Edition can be ... > So I can use SQL Developer 2008 to create a database in standard 2005, ...
    (microsoft.public.sqlserver.setup)
Loading