Re: Create a read only stored procedure

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

From: Gregory A. Larsen (greg.larsen_at_netzero.com)
Date: 08/18/04 

Date: Wed, 18 Aug 2004 10:26:55 -0700

Oh, I finally figured out what you where talking about. Yes it is true you
can still change the stored procedure using the ALTER statement. Although
it is also true that you can't use EM to change it.

Here is the code to prove that the ALTER can still be used to change the
code.

sp_MS_upd_sysobj_category 1
go
create proc sp_that_users_cant_change
as
print 'Try to change this message'
go
sp_MS_upd_sysobj_category 2
go
exec sp_that_users_cant_change
go
alter proc sp_that_users_cant_change
as
print 'I changed it'
go
exec sp_that_users_cant_change
go
sp_MS_upd_sysobj_category 1
go
drop proc sp_that_users_cant_change
go
sp_MS_upd_sysobj_category 2
go 

-- 
----------------------------------------------------------------------------
----------------------------------------------------------------------------
----
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Aaron [SQL Server MVP]" <ten.xoc@dnartreb.noraa> wrote in message
news:eqTj8RUhEHA.3932@TK2MSFTNGP09.phx.gbl...
> This didn't work on my system Greg, I was able to alter the procedure
> without getting a warning or error message.  8.00.952
>
> -- 
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
>
>
> "Gregory A. Larsen" <greg.larsen@netzero.com> wrote in message
> news:#TIrnMUhEHA.3320@TK2MSFTNGP11.phx.gbl...
> > You can use the following undocumented method of making to create a
system
> > stored procedure.  This should keep them from changing it.
> >
> > sp_MS_upd_sysobj_category 1
> > go
> > create proc sp_that_users_cant_change
> > as
> > print 'Try to change this message'
> > go
> > sp_MS_upd_sysobj_category 2
> >
> > -- 
> >
>
> --------------------------------------------------------------------------
> --
>
> --------------------------------------------------------------------------
> --
> > ----
> >
> > Need SQL Server Examples check out my website at
> > http://www.geocities.com/sqlserverexamples
> > "Heriberto" <heriberto@mathemathics.com.br> wrote in message
> > news:O6NLiCUhEHA.2544@TK2MSFTNGP10.phx.gbl...
> > > Hi, I would like to know how to create a stored procedure but not let
> the
> > > user edit it in Enterprise Manger like system's procedures. With the
> > option
> > > ENCRYPTION I cannot read it and my concern is just don't let the user
> > change
> > > it.
> > >
> > > Thanks
> > >
> > >
> >
> >
>
>

Relevant Pages

  • Re: Help with Update Query
    ... May I ask why this is ALTER ... As for the Update statement in the stored procedure, ... load the tables with sample data, in addition to all the Transact-SQL ... DDL, INSERTS, and T-SQL here and I'll take a look at it. ...
    (microsoft.public.access.queries)
  • Re: modifying stored procedure with SSMSE
    ... > What's different i SQL2005 is that when you open a stored procedure, ... > Instead it says "alter procedure....". ... > This works just like hitting "save" in MSSQL2000. ...
    (microsoft.public.sqlserver.tools)
  • Re: Precompiling checking problem
    ... alter table to drop column4 ... execute script to create the proc, ERROR cause it checks each command ... cant put go's between the commands in a stored procedure and even if you ...
    (microsoft.public.sqlserver.tools)
  • Re: modifying stored procedure with SSMSE
    ... I just needed help from the guys in here to ... What's different i SQL2005 is that when you open a stored procedure, ... Instead it says "alter procedure....". ... Prev by Date: ...
    (microsoft.public.sqlserver.tools)
  • Re: invalid column name
    ... procedure and then another stored procedure does some transformations on the ... The transformation processing generates new data(for ... fields(created previously by the alter table command). ...
    (microsoft.public.sqlserver.programming)