Re: Stored procedure update permission

From: Keith Kratochvil (sqlguy.back2u_at_comcast.net)
Date: 09/10/04 

Date: Fri, 10 Sep 2004 13:48:54 -0500

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: Stored procedure update permission
    ... SQL Server MVP ... >> Each developer has their own sql login and each developer has rights to ... >> that it will connect to the development database server using their ... >> stored procedures, ...
    (microsoft.public.sqlserver.server)
  • Re: Limiting T-SQL code as a .NET development standard
    ... Software Developer ... My last shop was full of Database developers who built almost all ... Stored Procedures, Views, function. ... code .NET Business/Data classes that spit out collections and have SQL ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: Stored procedure update permission
    ... >> Each developer has their own sql login and each developer has rights to ... >> that it will connect to the development database server using their ... When they execute procFoo SQL Server automatically tries to ... >> stored procedures, ...
    (microsoft.public.sqlserver.server)
  • Re: Stored procedure update permission
    ... dba collects the stored procedure changes and migrate to test and prodcution? ... > Each developer has their own sql login and each developer has rights to ... > stored procedures, ...
    (microsoft.public.sqlserver.server)
  • Database Role that allows execution of stored procedures?
    ... interaction with the database must be done through stored procedures i.e. ... and one would be if I could put the SQL login that the application uses into ... create my own role and grant it execute rights on each SP and no rights to ...
    (microsoft.public.sqlserver.server)
Loading