Re: Compiling Stored procedures

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Sumesh (Sumesh.Sundareswaran_at_trsga.com)
Date: 02/16/04 

Date: Mon, 16 Feb 2004 15:52:41 -0800

Thanks Mary for the quick response...that certainly is
a lot of good information.

This is the challenge I am facing though and
I can illustrate with an example so that it is more
meaningful, Developer A has DDL_ADMIN rights in the
development environment and no DDL_ADMIN rights in the UAT
(User Acceptance Testing) database:
1) Developer A creates a stored proc 'a' in Development
and tests it.
2) Developer A then requests the DBA team to create this
procedure in the UAT database. The source for creating
this proc is c:\a.sql
3) After a day or two, Developer A gets a request of a
bug found by users which he needs to fix.
4) Developer A fixes the bug in Deveopment, overwrites
c:\a.sql with the changes (this is tracked in VSS) and
requests DBA team to recompile the same procedure in UAT
database using a.sql.

This is the procedure we are following. We do not want
developers to have DDL_ADMIN rights in the UAT database
as that would seriously hamper our change management
procedures. But because of the frequent requests of the
developers to compile/recompile some of these procedures,
we need to find a way for them to do it on their own, as
that could save the entire team a lot of time.

Please advise,
Sumesh

>-----Original Message-----
>I'm not sure what you're asking. Do you want to grant
rights to a role
>to create and save stored procedures? If so, they need to
have rights
>on the base tables. Generally speaking, you want to have
everything
>owned by dbo, which you can do by running
sp_changeobjectowner stored
>procedure afterwards. You don't want to grant
administrative
>privileges unless absolutely necessary.
>
>-- Mary
>MCW Technologies
>http://www.mcwtech.com
>
>On Sat, 14 Feb 2004 09:40:57 -0800, "Sumesh"
><Sumesh.Sundareswaran@trsga.com> wrote:
>
>>Hi,
>>
>>Is there a way that I can grant rights to a certain role
>>or users to only compile stored procs, functions without
>>giving them ddl_admin rights?
>>
>>Thanks for all your help,
>>Sumesh
>
>.
>

Relevant Pages

  • Re: Admin rights via backdoors
    ... It seem like under the general assumption where the developer is using ... port, and when a probe or some other msg is sent to that port, the ... spawn a listener thread on the port ... I maintain that getting admin rights into an application while bypassing ...
    (Security-Basics)
  • User rights problem
    ... "Specified owner name 'dbo' either does not exist or you do not have ... rights which i should give to the "developer" login for donig so. ...
    (microsoft.public.sqlserver.security)
  • Re: Adminstrative rights
    ... they'd lose the ability to create things such as Windows Services, ... developer on the Windows platform must have Admin rights on the machine ... While developers need admin rights for some tasks, ... It's not or should not be the responsibility of the developer to be running in a situation at his or her workstation to be testing user rights. ...
    (comp.programming)
  • RE: Creating LIbrary
    ... u can create a sub area for this developer and assign him the amin of this ... then he creates the DLs on his area. ... If u have a dew developers, i suugest u to create a wss site for them and ... let them have right rights on this site. ...
    (microsoft.public.sharepoint.portalserver)
  • Re: Typical work day
    ... >> searching on Booleans (65 mins) ... developer and a network administrator. ... There was no IT manager. ... these programs to break easily plus new feature requests). ...
    (comp.programming)