Dropping System Stored Procedures

When a user database is created, there are 31 system stored procedures
created. In one of the SQL Server security auditing questions/feedback
session, it was suggested to drop all the system stored procedures from the
user database from security perspective. I have not found or seen any comment
on the web on this. Even Microsoft SQL Server 2000 Security Best Practices
does not state on this. Does someone has some comment on this?
> When a user database is created, there are 31 system stored procedures
> created. In one of the SQL Server security auditing questions/feedback
> session, it was suggested to drop all the system stored procedures from
> the
> user database from security perspective.
Why? System databases that are stored in master will still execute in the
context of the database they are called from.
A
|||Also if you drop your system stored procedures and your system does not
work, perhaps your configuration will not be supported by Microsoft.
Could you mention a couple of these stored procedures and its security risk?
Ben Nevarez
"Aaron Bertrand [SQL Server MVP]" wrote:

> Why? System databases that are stored in master will still execute in the
> context of the database they are called from.
> A
>
>
|||and what kind of security audit tool/application are you using.
Thanks, Liliya
"Ben Nevarez" wrote:
[vbcol=seagreen]
> Also if you drop your system stored procedures and your system does not
> work, perhaps your configuration will not be supported by Microsoft.
> Could you mention a couple of these stored procedures and its security risk?
> Ben Nevarez
>
>
> "Aaron Bertrand [SQL Server MVP]" wrote:
|||Thanks for all the feedback. Currently we are using Lumigent Log Explorer.
This generates DDL alerts. But this is not in the discussion. One of the DB
security/audit person asked us that we should drop all the system stored
procedures in the user database, not master database. I have never heard or
seen anything about this. If you have some knowledge from security
perspective, please let me know. Our IT Management is also keen to know about
it. Thanks again...Fraz
"Liliya Huff" wrote:
[vbcol=seagreen]
> and what kind of security audit tool/application are you using.
> --
> Thanks, Liliya
>
> "Ben Nevarez" wrote:
|||Fraz,
I heard this discussed many years ago by someone (long forgotten) as a
method of locking down a database. I never followed that advice because I
thought it was questionable.
Any change to the deliverable software, even changing permissions to it, has
to be carefully weighed for what it will break and which tools will no
longer work as expected.
I would not delete system stored procedure from a database unless I had a
very clear reason to do so and a good understanding of the impact.
(Actually, 'leave them alone' is how I read the subtext of other comments.)
RLF
"Fraz" <Fraz@.discussions.microsoft.com> wrote in message
news:45F11F19-110C-4E40-A4C1-811DC6B1BC8D@.microsoft.com...[vbcol=seagreen]
> Thanks for all the feedback. Currently we are using Lumigent Log Explorer.
> This generates DDL alerts. But this is not in the discussion. One of the
> DB
> security/audit person asked us that we should drop all the system stored
> procedures in the user database, not master database. I have never heard
> or
> seen anything about this. If you have some knowledge from security
> perspective, please let me know. Our IT Management is also keen to know
> about
> it. Thanks again...Fraz
> "Liliya Huff" wrote:
|||are you talking about SOX auditors?
that is an odd one. never showed up in our case...
dropping system stored procedures is not going to lock down your database
alone. Are you trying to lock down an insider or an outsider?
Thanks, Liliya
"Fraz" wrote:

> Thanks for all the feedback. Currently we are using Lumigent Log Explorer.
> This generates DDL alerts. But this is not in the discussion. One of the DB
> security/audit person asked us that we should drop all the system stored
> procedures in the user database, not master database. I have never heard or
> seen anything about this. If you have some knowledge from security
> perspective, please let me know. Our IT Management is also keen to know about
> it. Thanks again...Fraz
|||Not SOX auditors, they are other auditors. We are trying to protect the
database from oursider.
From all the comments I have received, I feel that it is safe to leave all
the system stored procedures in the user database as-it-is. I appreciate your
comments and thank you for this. Regards...Fraz
"Liliya Huff" wrote:

> are you talking about SOX auditors?
> that is an odd one. never showed up in our case...
> dropping system stored procedures is not going to lock down your database
> alone. Are you trying to lock down an insider or an outsider?
> --
> Thanks, Liliya
>
> "Fraz" wrote:
>
|||if it is an insider attack, then killing system stored procedures is not
going to protect you.
Insider will be trying to get access to your data or if really upset for
example generate a dos kind of attack. On inside an authenticated attack is
your likely attack. Find out where your end-users stash their passwords .
In both cases killing system stored procedures is not going to to anything
except of a trouble for you to locate who when and how. There is no need to
use any of system stored procedures to create an authenticated dos attack,
public is more then enough. To take the data - that one is likely to be an
authenticated attack, because it is more difficult to catch, imitates a real
application.
Thanks, Liliya
"Fraz" wrote:

> Not SOX auditors, they are other auditors. We are trying to protect the
> database from oursider.
> From all the comments I have received, I feel that it is safe to leave all
> the system stored procedures in the user database as-it-is. I appreciate your
> comments and thank you for this. Regards...Fraz

Dropping System Stored Procedures

When a user database is created, there are 31 system stored procedures
created. In one of the SQL Server security auditing questions/feedback
session, it was suggested to drop all the system stored procedures from the
user database from security perspective. I have not found or seen any comment
on the web on this. Even Microsoft SQL Server 2000 Security Best Practices
does not state on this. Does someone has some comment on this?> When a user database is created, there are 31 system stored procedures
> created. In one of the SQL Server security auditing questions/feedback
> session, it was suggested to drop all the system stored procedures from
> the
> user database from security perspective.
Why? System databases that are stored in master will still execute in the
context of the database they are called from.
A|||Also if you drop your system stored procedures and your system does not
work, perhaps your configuration will not be supported by Microsoft.
Could you mention a couple of these stored procedures and its security risk?
Ben Nevarez
"Aaron Bertrand [SQL Server MVP]" wrote:
> > When a user database is created, there are 31 system stored procedures
> > created. In one of the SQL Server security auditing questions/feedback
> > session, it was suggested to drop all the system stored procedures from
> > the
> > user database from security perspective.
> Why? System databases that are stored in master will still execute in the
> context of the database they are called from.
> A
>
>|||and what kind of security audit tool/application are you using.
--
Thanks, Liliya
"Ben Nevarez" wrote:
> Also if you drop your system stored procedures and your system does not
> work, perhaps your configuration will not be supported by Microsoft.
> Could you mention a couple of these stored procedures and its security risk?
> Ben Nevarez
>
>
> "Aaron Bertrand [SQL Server MVP]" wrote:
> > > When a user database is created, there are 31 system stored procedures
> > > created. In one of the SQL Server security auditing questions/feedback
> > > session, it was suggested to drop all the system stored procedures from
> > > the
> > > user database from security perspective.
> >
> > Why? System databases that are stored in master will still execute in the
> > context of the database they are called from.
> >
> > A
> >
> >
> >|||Thanks for all the feedback. Currently we are using Lumigent Log Explorer.
This generates DDL alerts. But this is not in the discussion. One of the DB
security/audit person asked us that we should drop all the system stored
procedures in the user database, not master database. I have never heard or
seen anything about this. If you have some knowledge from security
perspective, please let me know. Our IT Management is also keen to know about
it. Thanks again...Fraz
"Liliya Huff" wrote:
> and what kind of security audit tool/application are you using.
> --
> Thanks, Liliya
>
> "Ben Nevarez" wrote:
> >
> > Also if you drop your system stored procedures and your system does not
> > work, perhaps your configuration will not be supported by Microsoft.
> >
> > Could you mention a couple of these stored procedures and its security risk?
> >
> > Ben Nevarez
> >
> >
> >
> >
> > "Aaron Bertrand [SQL Server MVP]" wrote:
> >
> > > > When a user database is created, there are 31 system stored procedures
> > > > created. In one of the SQL Server security auditing questions/feedback
> > > > session, it was suggested to drop all the system stored procedures from
> > > > the
> > > > user database from security perspective.
> > >
> > > Why? System databases that are stored in master will still execute in the
> > > context of the database they are called from.
> > >
> > > A
> > >
> > >
> > >|||Fraz,
I heard this discussed many years ago by someone (long forgotten) as a
method of locking down a database. I never followed that advice because I
thought it was questionable.
Any change to the deliverable software, even changing permissions to it, has
to be carefully weighed for what it will break and which tools will no
longer work as expected.
I would not delete system stored procedure from a database unless I had a
very clear reason to do so and a good understanding of the impact.
(Actually, 'leave them alone' is how I read the subtext of other comments.)
RLF
"Fraz" <Fraz@.discussions.microsoft.com> wrote in message
news:45F11F19-110C-4E40-A4C1-811DC6B1BC8D@.microsoft.com...
> Thanks for all the feedback. Currently we are using Lumigent Log Explorer.
> This generates DDL alerts. But this is not in the discussion. One of the
> DB
> security/audit person asked us that we should drop all the system stored
> procedures in the user database, not master database. I have never heard
> or
> seen anything about this. If you have some knowledge from security
> perspective, please let me know. Our IT Management is also keen to know
> about
> it. Thanks again...Fraz
> "Liliya Huff" wrote:
>> and what kind of security audit tool/application are you using.
>> --
>> Thanks, Liliya
>>
>> "Ben Nevarez" wrote:
>> >
>> > Also if you drop your system stored procedures and your system does not
>> > work, perhaps your configuration will not be supported by Microsoft.
>> >
>> > Could you mention a couple of these stored procedures and its security
>> > risk?
>> >
>> > Ben Nevarez
>> >
>> >
>> >
>> >
>> > "Aaron Bertrand [SQL Server MVP]" wrote:
>> >
>> > > > When a user database is created, there are 31 system stored
>> > > > procedures
>> > > > created. In one of the SQL Server security auditing
>> > > > questions/feedback
>> > > > session, it was suggested to drop all the system stored procedures
>> > > > from
>> > > > the
>> > > > user database from security perspective.
>> > >
>> > > Why? System databases that are stored in master will still execute
>> > > in the
>> > > context of the database they are called from.
>> > >
>> > > A
>> > >
>> > >
>> > >|||are you talking about SOX auditors?
that is an odd one. never showed up in our case...
dropping system stored procedures is not going to lock down your database
alone. Are you trying to lock down an insider or an outsider?
--
Thanks, Liliya
"Fraz" wrote:
> Thanks for all the feedback. Currently we are using Lumigent Log Explorer.
> This generates DDL alerts. But this is not in the discussion. One of the DB
> security/audit person asked us that we should drop all the system stored
> procedures in the user database, not master database. I have never heard or
> seen anything about this. If you have some knowledge from security
> perspective, please let me know. Our IT Management is also keen to know about
> it. Thanks again...Fraz|||Not SOX auditors, they are other auditors. We are trying to protect the
database from oursider.
From all the comments I have received, I feel that it is safe to leave all
the system stored procedures in the user database as-it-is. I appreciate your
comments and thank you for this. Regards...Fraz
"Liliya Huff" wrote:
> are you talking about SOX auditors?
> that is an odd one. never showed up in our case...
> dropping system stored procedures is not going to lock down your database
> alone. Are you trying to lock down an insider or an outsider?
> --
> Thanks, Liliya
>
> "Fraz" wrote:
> > Thanks for all the feedback. Currently we are using Lumigent Log Explorer.
> > This generates DDL alerts. But this is not in the discussion. One of the DB
> > security/audit person asked us that we should drop all the system stored
> > procedures in the user database, not master database. I have never heard or
> > seen anything about this. If you have some knowledge from security
> > perspective, please let me know. Our IT Management is also keen to know about
> > it. Thanks again...Fraz
>|||if it is an insider attack, then killing system stored procedures is not
going to protect you.
Insider will be trying to get access to your data or if really upset for
example generate a dos kind of attack. On inside an authenticated attack is
your likely attack. Find out where your end-users stash their passwords :).
In both cases killing system stored procedures is not going to to anything
except of a trouble for you to locate who when and how. There is no need to
use any of system stored procedures to create an authenticated dos attack,
public is more then enough. To take the data - that one is likely to be an
authenticated attack, because it is more difficult to catch, imitates a real
application.
--
Thanks, Liliya
"Fraz" wrote:
> Not SOX auditors, they are other auditors. We are trying to protect the
> database from oursider.
> From all the comments I have received, I feel that it is safe to leave all
> the system stored procedures in the user database as-it-is. I appreciate your
> comments and thank you for this. Regards...Frazsql

Dropping stored procedures

Hello!
I've written a script to drop all the SPs from a given DB:
DECLARE @.name nvarchar(200)
DECLARE list CURSOR FOR
SELECT name FROM sysobjects where xtype = 'p'
OPEN list
FETCH NEXT FROM list INTO @.name
WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.name = 'drop procedure ' + @.name
EXEC sp_executesql @.name
FETCH NEXT FROM list INTO @.name
END
CLOSE list
DEALLOCATE list
GO
When I'm running it from Query Analyzer it works fine, but when running it
from a small utility (which all id does is opening a connection to the DB
and running the same script), only some of the SPs are dropped.
I'm now looking at the utility, but is there any problem with the SQL scirpt
(or a better way to the task (drop all of the SPs in a DB, without knowing
the names of them all)).
Thanks!
RoeeTry to explicitly spcify the owner of the procedure in the Drop command.
--
Dejan Sarka, SQL Server MVP
Please reply only to the newsgroups.
"Roee Friedman" <necnecnecnec@.hotmail.com> wrote in message
news:bppqas$1rcn6n$1@.ID-200860.news.uni-berlin.de...
> Hello!
> I've written a script to drop all the SPs from a given DB:
> DECLARE @.name nvarchar(200)
> DECLARE list CURSOR FOR
> SELECT name FROM sysobjects where xtype = 'p'
> OPEN list
> FETCH NEXT FROM list INTO @.name
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> SET @.name = 'drop procedure ' + @.name
> EXEC sp_executesql @.name
> FETCH NEXT FROM list INTO @.name
> END
> CLOSE list
> DEALLOCATE list
> GO
> When I'm running it from Query Analyzer it works fine, but when running it
> from a small utility (which all id does is opening a connection to the DB
> and running the same script), only some of the SPs are dropped.
> I'm now looking at the utility, but is there any problem with the SQL
scirpt
> (or a better way to the task (drop all of the SPs in a DB, without knowing
> the names of them all)).
> Thanks!
> Roee
>
>

Drop stored procedures by...

Hi,
How do I drop all stored procedures with the prefix of "www_".
Basically, I need to add all new dev DB sprocs to our prod DB, but on the
prod DB there are a whole bunch of old sprocs that needs to be dropped.
Thanks in advance
ChristianFrom Vyas:
On SQL Server 2000 the following will give you a list of all stored
procedure names in the database:
SELECT ROUTINE_SCHEMA AS Owner,
ROUTINE_NAME AS ProcedureName
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE = 'PROCEDURE'
The following will return the DROP PROC commands that you need to run:
SELECT 'DROP PROC ' + QUOTENAME(ROUTINE_SCHEMA) + '.' +
QUOTENAME(ROUTINE_NAME) AS Command
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE = 'PROCEDURE'
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Christian Perthen" <abracadabara@.dontreplytothidress.com> schrieb im
Newsbeitrag news:e2VlEOESFHA.248@.TK2MSFTNGP15.phx.gbl...
> Hi,
> How do I drop all stored procedures with the prefix of "www_".
> Basically, I need to add all new dev DB sprocs to our prod DB, but on the
> prod DB there are a whole bunch of old sprocs that needs to be dropped.
> Thanks in advance
> Christian
>|||Must be sure changed in some way, sorry i forgot:

> SELECT ROUTINE_SCHEMA AS Owner,
> ROUTINE_NAME AS ProcedureName
> FROM INFORMATION_SCHEMA.ROUTINES
> WHERE ROUTINE_TYPE = 'PROCEDURE' AND ROUTINE_NAME LIKE 'www_%'
> The following will return the DROP PROC commands that you need to run:
> SELECT 'DROP PROC ' + QUOTENAME(ROUTINE_SCHEMA) + '.' +
> QUOTENAME(ROUTINE_NAME) AS Command
> FROM INFORMATION_SCHEMA.ROUTINES
> WHERE ROUTINE_TYPE = 'PROCEDURE' AND ROUTINE_NAME LIKE 'www_%'
>
Jens Suessmeyer.
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> schrieb
im Newsbeitrag news:uLPtCWESFHA.1996@.TK2MSFTNGP10.phx.gbl...
> From Vyas:
> On SQL Server 2000 the following will give you a list of all stored
> procedure names in the database:
> SELECT ROUTINE_SCHEMA AS Owner,
> ROUTINE_NAME AS ProcedureName
> FROM INFORMATION_SCHEMA.ROUTINES
> WHERE ROUTINE_TYPE = 'PROCEDURE' AND ROUTINE_NAME LIKE 'www_%'
> The following will return the DROP PROC commands that you need to run:
> SELECT 'DROP PROC ' + QUOTENAME(ROUTINE_SCHEMA) + '.' +
> QUOTENAME(ROUTINE_NAME) AS Command
> FROM INFORMATION_SCHEMA.ROUTINES
> WHERE ROUTINE_TYPE = 'PROCEDURE' AND ROUTINE_NAME LIKE 'www_%'
>
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "Christian Perthen" <abracadabara@.dontreplytothidress.com> schrieb im
> Newsbeitrag news:e2VlEOESFHA.248@.TK2MSFTNGP15.phx.gbl...
>

DROP PROCS

Is it necessary to drop & recreate all procedures and triggers every
few months? If yes, why
?.
No; where did you hear that?
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
"docsql" <docsql@.noemail.nospam> wrote in message
news:u%23J7LRH7FHA.956@.TK2MSFTNGP10.phx.gbl...
> Is it necessary to drop & recreate all procedures and triggers every
> few months? If yes, why
> ?.
>
>
|||Actually it was a question on a supplemental questionnaire for a DBA
position. Do you think it might be true for other databases?
Sybase/oracle?
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:uCEDuSH7FHA.3976@.TK2MSFTNGP15.phx.gbl...
> No; where did you hear that?
>
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>
> "docsql" <docsql@.noemail.nospam> wrote in message
> news:u%23J7LRH7FHA.956@.TK2MSFTNGP10.phx.gbl...
>
|||Sounds like a trick question to me. Your other questions, too. I would be
very cautious about taking this job if I were you.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
"docsql" <docsql@.noemail.nospam> wrote in message
news:%231jyCmH7FHA.3200@.TK2MSFTNGP11.phx.gbl...
> Actually it was a question on a supplemental questionnaire for a DBA
> position. Do you think it might be true for other databases?
> Sybase/oracle?
>
> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
> news:uCEDuSH7FHA.3976@.TK2MSFTNGP15.phx.gbl...
>
|||By the way, they might be looking for recompilation (perhaps whoever wrote
the test didn't know how to recompile stored procedures and thought they had
to be dropped and re-created?) ... That's my only guess.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
"docsql" <docsql@.noemail.nospam> wrote in message
news:%231jyCmH7FHA.3200@.TK2MSFTNGP11.phx.gbl...
> Actually it was a question on a supplemental questionnaire for a DBA
> position. Do you think it might be true for other databases?
> Sybase/oracle?
>
> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
> news:uCEDuSH7FHA.3976@.TK2MSFTNGP15.phx.gbl...
>

DROP PROCS

Is it necessary to drop & recreate all procedures and triggers every
few months? If yes, why
?.No; where did you hear that?
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"docsql" <docsql@.noemail.nospam> wrote in message
news:u%23J7LRH7FHA.956@.TK2MSFTNGP10.phx.gbl...
> Is it necessary to drop & recreate all procedures and triggers every
> few months? If yes, why
> ?.
>
>|||Actually it was a question on a supplemental questionnaire for a DBA
position. Do you think it might be true for other databases?
Sybase/oracle?
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:uCEDuSH7FHA.3976@.TK2MSFTNGP15.phx.gbl...
> No; where did you hear that?
>
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>
> "docsql" <docsql@.noemail.nospam> wrote in message
> news:u%23J7LRH7FHA.956@.TK2MSFTNGP10.phx.gbl...
>> Is it necessary to drop & recreate all procedures and triggers every
>> few months? If yes, why
>> ?.
>>
>|||Sounds like a trick question to me. Your other questions, too. I would be
very cautious about taking this job if I were you.
--
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"docsql" <docsql@.noemail.nospam> wrote in message
news:%231jyCmH7FHA.3200@.TK2MSFTNGP11.phx.gbl...
> Actually it was a question on a supplemental questionnaire for a DBA
> position. Do you think it might be true for other databases?
> Sybase/oracle?
>
> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
> news:uCEDuSH7FHA.3976@.TK2MSFTNGP15.phx.gbl...
>> No; where did you hear that?
>>
>> --
>> Adam Machanic
>> Pro SQL Server 2005, available now
>> http://www.apress.com/book/bookDisplay.html?bID=457
>> --
>>
>> "docsql" <docsql@.noemail.nospam> wrote in message
>> news:u%23J7LRH7FHA.956@.TK2MSFTNGP10.phx.gbl...
>> Is it necessary to drop & recreate all procedures and triggers
>> every few months? If yes, why
>> ?.
>>
>>
>|||By the way, they might be looking for recompilation (perhaps whoever wrote
the test didn't know how to recompile stored procedures and thought they had
to be dropped and re-created?) ... That's my only guess.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"docsql" <docsql@.noemail.nospam> wrote in message
news:%231jyCmH7FHA.3200@.TK2MSFTNGP11.phx.gbl...
> Actually it was a question on a supplemental questionnaire for a DBA
> position. Do you think it might be true for other databases?
> Sybase/oracle?
>
> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
> news:uCEDuSH7FHA.3976@.TK2MSFTNGP15.phx.gbl...
>> No; where did you hear that?
>>
>> --
>> Adam Machanic
>> Pro SQL Server 2005, available now
>> http://www.apress.com/book/bookDisplay.html?bID=457
>> --
>>
>> "docsql" <docsql@.noemail.nospam> wrote in message
>> news:u%23J7LRH7FHA.956@.TK2MSFTNGP10.phx.gbl...
>> Is it necessary to drop & recreate all procedures and triggers
>> every few months? If yes, why
>> ?.
>>
>>
>

DROP PROCS

Is it necessary to drop & recreate all procedures and triggers every
few months? If yes, why
?.No; where did you hear that?
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"docsql" <docsql@.noemail.nospam> wrote in message
news:u%23J7LRH7FHA.956@.TK2MSFTNGP10.phx.gbl...
> Is it necessary to drop & recreate all procedures and triggers every
> few months? If yes, why
> ?.
>
>|||Actually it was a question on a supplemental questionnaire for a DBA
position. Do you think it might be true for other databases?
Sybase/oracle?
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:uCEDuSH7FHA.3976@.TK2MSFTNGP15.phx.gbl...
> No; where did you hear that?
>
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>
> "docsql" <docsql@.noemail.nospam> wrote in message
> news:u%23J7LRH7FHA.956@.TK2MSFTNGP10.phx.gbl...
>|||Sounds like a trick question to me. Your other questions, too. I would be
very cautious about taking this job if I were you.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"docsql" <docsql@.noemail.nospam> wrote in message
news:%231jyCmH7FHA.3200@.TK2MSFTNGP11.phx.gbl...
> Actually it was a question on a supplemental questionnaire for a DBA
> position. Do you think it might be true for other databases?
> Sybase/oracle?
>
> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
> news:uCEDuSH7FHA.3976@.TK2MSFTNGP15.phx.gbl...
>|||By the way, they might be looking for recompilation (perhaps whoever wrote
the test didn't know how to recompile stored procedures and thought they had
to be dropped and re-created?) ... That's my only guess.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"docsql" <docsql@.noemail.nospam> wrote in message
news:%231jyCmH7FHA.3200@.TK2MSFTNGP11.phx.gbl...
> Actually it was a question on a supplemental questionnaire for a DBA
> position. Do you think it might be true for other databases?
> Sybase/oracle?
>
> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
> news:uCEDuSH7FHA.3976@.TK2MSFTNGP15.phx.gbl...
>

Drop Create vs Alter in regards to stored procedures

Anyone have any compelling arguements for using one over the other?

Thanks

Mercy

I'm not sure what the argument would be.

That's like asking if there's an argument to use SELECT, INSERT, UPDATE, OR DELETE.

Adamus

|||

Adamus Turner wrote:

I'm not sure what the argument would be.

That's like asking if there's an argument to use SELECT, INSERT, UPDATE, OR DELETE.

Adamus

I am not sure what your saying.

The question is:

Is there any reason to use

'IF Procedure Exists DROP

Create Procedure'

as opposed to

'ALTER Procedure'

Mercy

|||

I still don't understand what the argument would be. It's purely based on your desired result.

Adamus

|||If the user owns an object and has had create privileges revoked since gaining ownership of the object the ONLY way that the user can make changes is with the ALTER command. This doesn't come up frequently, but it has come a few times.|||

ALTERing a procedure keeps the permissions the same, while DROP and CREATE will require you to set the permissions on the now new stored procedure.

In SQL 2000 and before some people prefer to use DROP and CREATE so that they can see when the procedures were last updated.

Personally I prefer the ALTER method so that any undocumented permissions are preserved instead of destroyed.

Drop all strored procedures

Hello,
How do I write an sql query that drops all stored procedures
in a database if I do not know how many there are or what
they are called?
--
Mikael EngdahlYou could use a cursor and loop through INFORMATION_SCHEMA.ROUTINES WHERE
ROUTINE_TYPE='PROCEDURE', then EXEC('DROP PROC '+@.routine_name) within the
loop...
"Mikael Engdahl" <mikael-l@.engdahl.no.spam.com> wrote in message
news:eyvDqK0ZDHA.2668@.TK2MSFTNGP09.phx.gbl...
> Hello,
> How do I write an sql query that drops all stored procedures
> in a database if I do not know how many there are or what
> they are called?
>
> --
> Mikael Engdahl
>