SQL Server, Stored procedure update permission

microsoft.public.sqlserver.server - Microsoft SQL Server forum discussing administration and other SQL Server related topics. MS SQL Server is used for high performance and enterprise level applications.


SQL Server >> Stored procedure update permission

by Keith Kratochvil » Sat, 11 Sep 2004 02:48:54 GMT

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" < XXXX@XXXXX.COM > wrote in message
news: XXXX@XXXXX.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?


Top

SQL Server >> Stored procedure update permission

by Keith Kratochvil » Sat, 11 Sep 2004 04:04:32 GMT


Code gets promoted to test, staging, and production by the DBA. Simply grab
the appropriate stored procedure text files from Visual Source Safe and
execute them within the appropriate environment.

--
Keith




the
prodcution?

so
specific
owned
to


let
or
stored
the
but
change
to
Is



Top

SQL Server >> Stored procedure update permission

by Dan Guzman » Sat, 11 Sep 2004 23:09:52 GMT

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









Top
Similar Threads

1. Update Permission on Stored Procedures - SQL Server

2. Triggers, Stored Procedures and user permission to call an Extended Procedure

3. Stored Procedure for update : update selected fields depending on indicators - SQL Server

4. Stored Procedure for update : update selected fields depending on 

Something like ...

CREATE PROCEDURE TestProc
(
@indicator1 int
,@indicator2 int
,@field1 varchar(50)
,@field2 varchar(50)
,@keyfield int
)
AS

IF @indicator1 = 1 THEN
   UPDATE MyTable SET Field1 = @field1
   WHERE keyfield = @keyfield

IF @indicator2 = 1 THEN
   UPDATE MyTable SET Field1 = @field2
   WHERE keyfield = @keyfield


-- 
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

***************************
Think Outside the Box!
***************************


" XXXX@XXXXX.COM " wrote:

> Hello,
> 
> 
> is it possible to create a stored procedure with parameters,
> so that you can say for example :
> 
> update table
> 
> if @indicator1 = 1  then set field1 = @field1
> if @indicator2 = 1  then set field2 = @field2
> .....
> 
> where keyfield = @keyfield ? 
> 
> 
> thank you, 
> kind regards , 
> 
> sven
> 
>

5. PRB: INSTEAD OF UPDATE trigger fails when UPDATE is done from an stored procedure - SQL Server

6. Calling a stored procedure from a stored procedure and ignoring output from inner procedure call 

Hello there,

I am executing a stored procedure from inside my own procedure. The procedure I am calling returns a resultset and this messes up the resultsets 
   from my procedure. Is there a way to ignore the output from a stored procedure? (like set nocount on?)

Best

7. Checking permission rights using SQL Server Stored Procedure and return values - Access Forms Programming

8. Stored procedure permissions with xp_cmdshell on SQL 6.5 

Is there any way to allow a user to use the xp_cmdshell extended
stored procedure without giving that user execute permissions to
xp_cmdshell in SQL server 6.5?  Let me clarify.  Lets say I (as the
dbo) create a stored procedure called sp_send_err:

CREATE PROCEDURE sp_send_err @CompID varchar(20) AS
declare @strCMD varchar(255)
select @strCMD = "master.dbo.xp_cmdshell 'net send " + @CompID + "
""ERROR!""', no_output"
execute (@strCMD)
GO

Now lest say I give "user1" execute permissions on sp_send_err, but no
permissions on xp_cmdshell.  When I run sp_send_error I get the
following error:

"EXECUTE permission denied on object xp_cmdshell, database master,
owner dbo".

Why doesn't this work?  What else can I do?