Is it better to drop temporary tables at the end of a stored procedure?
I have heard somewhere that there can be stored proc recompilation issues which affect performance when you explicitly drop temp tables?
Will SQL Server "Clean Up" once the stored proc is out of scope or should I "do it myself".
Which method has known performance benefits?
CheersHi,
If resources arent an issue on your sqlserver you dont need to drop temps yourself. sqlserver will drop them when the sp is done.
Be careful though when working with temps, more than five updates to a temp table could force sqlserver to recomple. Any Set statements in the sp will also force sqlserver to recompile(SET NOCOUNT ON for example).
Thursday, March 29, 2012
Dropping tempe tables
My query creates a new temp table every time a report is run. I need to check
if the table exists before I drop it and perform a select into. Since SQL
Server appends an ID (session ID?) to the temp table name, I am unable to
drop the table prior to running the query. Is there a better way to handle
this? Any help would be appreciated.Try this. make sure the temp table does not exist, and you can do it this way.
Drop your new table at the end if your query.
CREATE PROCEDURE [dbo].[Test] AS
Select top 5 * into #temp from Orders
Select * from #temp
Drop Table #temp
GO
"DrM" wrote:
> My query creates a new temp table every time a report is run. I need to check
> if the table exists before I drop it and perform a select into. Since SQL
> Server appends an ID (session ID?) to the temp table name, I am unable to
> drop the table prior to running the query. Is there a better way to handle
> this? Any help would be appreciated.
if the table exists before I drop it and perform a select into. Since SQL
Server appends an ID (session ID?) to the temp table name, I am unable to
drop the table prior to running the query. Is there a better way to handle
this? Any help would be appreciated.Try this. make sure the temp table does not exist, and you can do it this way.
Drop your new table at the end if your query.
CREATE PROCEDURE [dbo].[Test] AS
Select top 5 * into #temp from Orders
Select * from #temp
Drop Table #temp
GO
"DrM" wrote:
> My query creates a new temp table every time a report is run. I need to check
> if the table exists before I drop it and perform a select into. Since SQL
> Server appends an ID (session ID?) to the temp table name, I am unable to
> drop the table prior to running the query. Is there a better way to handle
> this? Any help would be appreciated.
Dropping tables from a article
Just a quick question , when you have a article is their any way to drop a table from a article.
Don't know if it important but I'm using merge replication.Ok....what's an article?|||Originally posted by Brett Kaiser
Ok....what's an article?
Sorry I suppose that should have been described as how do I drop a article from a publication. When the article is a table, or is a table a group of articles. Not hundered percent on the Publishing Metaphor.
A article is part of a publication that is part use as part of replication.
Any suggestions?|||Sorry...haven't done replication, but looking up in bol, it looks like the correct terminology...
Did you check out BOL?
To delete an article
Note Deleting articles from publications that have subscriptions is not allowed. To delete an article, you must first delete all subscriptions to the publication.
At the Publisher, open SQL Server Enterprise Manager, expand a server group, expand the Replication folder, expand the Publications folder, right-click the publication, and then click Properties.
Click the Articles tab, select an article to delete, and then clear the check box next to the article to delete.
1988-2000 Microsoft Corporation. All Rights Reserved.|||I don't think there is. What I usually do is drop and recreate the article if I want to make a change.|||Originally posted by joejcheng
I don't think there is. What I usually do is drop and recreate the article if I want to make a change.
Brill, Just wanted to be sure. Thankssql
Don't know if it important but I'm using merge replication.Ok....what's an article?|||Originally posted by Brett Kaiser
Ok....what's an article?
Sorry I suppose that should have been described as how do I drop a article from a publication. When the article is a table, or is a table a group of articles. Not hundered percent on the Publishing Metaphor.
A article is part of a publication that is part use as part of replication.
Any suggestions?|||Sorry...haven't done replication, but looking up in bol, it looks like the correct terminology...
Did you check out BOL?
To delete an article
Note Deleting articles from publications that have subscriptions is not allowed. To delete an article, you must first delete all subscriptions to the publication.
At the Publisher, open SQL Server Enterprise Manager, expand a server group, expand the Replication folder, expand the Publications folder, right-click the publication, and then click Properties.
Click the Articles tab, select an article to delete, and then clear the check box next to the article to delete.
1988-2000 Microsoft Corporation. All Rights Reserved.|||I don't think there is. What I usually do is drop and recreate the article if I want to make a change.|||Originally posted by joejcheng
I don't think there is. What I usually do is drop and recreate the article if I want to make a change.
Brill, Just wanted to be sure. Thankssql
dropping table from publication
I have setup transactional replication and its going fine since some time in
testing. I was trying to remove one of the tables from publication, but it
always says that i have to drop subscription inorder to drop table from
publication.
If I drop subscription and recreate new subscription after the table is
dropped, I will be out of synch. with publisher database as publisher is
still on-line.
Is there any easy way to get arround this...
Regards,
Ravi
Hi Ravi,
Not sure if I fully understand your situation but you can definitely drop
the subscription for just the table that you want to drop using
sp_dropsubscription (See Books Online). The UI (SEM & SMSS) doesn't usually
allow you to perform such article-level operation however.
HTH
-Raymond
"SQL Replication Guy" <SQLReplicationGuy@.discussions.microsoft.com> wrote in
message news:EED2FB1D-7481-4027-B418-55BD7A97322E@.microsoft.com...
>I have setup transactional replication and its going fine since some time
>in
> testing. I was trying to remove one of the tables from publication, but it
> always says that i have to drop subscription inorder to drop table from
> publication.
> If I drop subscription and recreate new subscription after the table is
> dropped, I will be out of synch. with publisher database as publisher is
> still on-line.
> Is there any easy way to get arround this...
> Regards,
> Ravi
>
|||Raymond thanks for the reply, all i ma trying to do is drop one table from
existing publication and subscription.
and if possible add another table to same publication and subscription...
Regards,
Ravi
"Raymond Mak [MSFT]" wrote:
> Hi Ravi,
> Not sure if I fully understand your situation but you can definitely drop
> the subscription for just the table that you want to drop using
> sp_dropsubscription (See Books Online). The UI (SEM & SMSS) doesn't usually
> allow you to perform such article-level operation however.
> HTH
> -Raymond
> "SQL Replication Guy" <SQLReplicationGuy@.discussions.microsoft.com> wrote in
> message news:EED2FB1D-7481-4027-B418-55BD7A97322E@.microsoft.com...
>
>
|||Ravi,
I used this to change a column on a replicated table. It is almost exactly
the script you'll need:
exec sp_dropsubscription @.publication = 'tTestFNames'
, @.article = 'tEmployees'
, @.subscriber = 'RSCOMPUTER'
, @.destination_db = 'testrep'
exec sp_droparticle @.publication = 'tTestFNames'
, @.article = 'tEmployees'
alter table tEmployees alter column Forename varchar(100) null
exec sp_addarticle @.publication = 'tTestFNames'
, @.article = 'tEmployees'
, @.source_table = 'tEmployees'
exec sp_addsubscription @.publication = 'tTestFNames'
, @.article = 'tEmployees'
, @.subscriber = 'RSCOMPUTER'
, @.destination_db = 'testrep'
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Hi Ravi,
I keep forgetting that folks may be using Merge replication in which case it
is not possible (read: no supported way) to drop a single article without
reinitializing the entire subscription. But in case you are using
transactional\snapshot replication, I think here is what you need to do:
1) Call sp_dropsubscription to drop subscriptions on the article you want to
drop
2) Call sp_droparticle to drop the article you want to drop
3) Call sp_addarticle to add the new table to your publication
4) Call sp_addsubscrption to add subscription to your new table (this is not
necessary if syspublications.immediate_sync = 1)
5) Run snapshot agent to generate snapshot for the new article, run
distribution agent to apply it
You probably know about this already but dropping subscription on an article
doesn't remove data that has already been replicated to the subscriber. Of
course, you should be able to manually drop the table easily at the
subscriber if that is what you want.
-Raymond
"SQL Replication Guy" <SQLReplicationGuy@.discussions.microsoft.com> wrote in
message news:CBE581C0-3897-4A40-A740-CB38D33D61C5@.microsoft.com...[vbcol=seagreen]
> Raymond thanks for the reply, all i ma trying to do is drop one table from
> existing publication and subscription.
> and if possible add another table to same publication and subscription...
> Regards,
> Ravi
> "Raymond Mak [MSFT]" wrote:
testing. I was trying to remove one of the tables from publication, but it
always says that i have to drop subscription inorder to drop table from
publication.
If I drop subscription and recreate new subscription after the table is
dropped, I will be out of synch. with publisher database as publisher is
still on-line.
Is there any easy way to get arround this...
Regards,
Ravi
Hi Ravi,
Not sure if I fully understand your situation but you can definitely drop
the subscription for just the table that you want to drop using
sp_dropsubscription (See Books Online). The UI (SEM & SMSS) doesn't usually
allow you to perform such article-level operation however.
HTH
-Raymond
"SQL Replication Guy" <SQLReplicationGuy@.discussions.microsoft.com> wrote in
message news:EED2FB1D-7481-4027-B418-55BD7A97322E@.microsoft.com...
>I have setup transactional replication and its going fine since some time
>in
> testing. I was trying to remove one of the tables from publication, but it
> always says that i have to drop subscription inorder to drop table from
> publication.
> If I drop subscription and recreate new subscription after the table is
> dropped, I will be out of synch. with publisher database as publisher is
> still on-line.
> Is there any easy way to get arround this...
> Regards,
> Ravi
>
|||Raymond thanks for the reply, all i ma trying to do is drop one table from
existing publication and subscription.
and if possible add another table to same publication and subscription...
Regards,
Ravi
"Raymond Mak [MSFT]" wrote:
> Hi Ravi,
> Not sure if I fully understand your situation but you can definitely drop
> the subscription for just the table that you want to drop using
> sp_dropsubscription (See Books Online). The UI (SEM & SMSS) doesn't usually
> allow you to perform such article-level operation however.
> HTH
> -Raymond
> "SQL Replication Guy" <SQLReplicationGuy@.discussions.microsoft.com> wrote in
> message news:EED2FB1D-7481-4027-B418-55BD7A97322E@.microsoft.com...
>
>
|||Ravi,
I used this to change a column on a replicated table. It is almost exactly
the script you'll need:
exec sp_dropsubscription @.publication = 'tTestFNames'
, @.article = 'tEmployees'
, @.subscriber = 'RSCOMPUTER'
, @.destination_db = 'testrep'
exec sp_droparticle @.publication = 'tTestFNames'
, @.article = 'tEmployees'
alter table tEmployees alter column Forename varchar(100) null
exec sp_addarticle @.publication = 'tTestFNames'
, @.article = 'tEmployees'
, @.source_table = 'tEmployees'
exec sp_addsubscription @.publication = 'tTestFNames'
, @.article = 'tEmployees'
, @.subscriber = 'RSCOMPUTER'
, @.destination_db = 'testrep'
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Hi Ravi,
I keep forgetting that folks may be using Merge replication in which case it
is not possible (read: no supported way) to drop a single article without
reinitializing the entire subscription. But in case you are using
transactional\snapshot replication, I think here is what you need to do:
1) Call sp_dropsubscription to drop subscriptions on the article you want to
drop
2) Call sp_droparticle to drop the article you want to drop
3) Call sp_addarticle to add the new table to your publication
4) Call sp_addsubscrption to add subscription to your new table (this is not
necessary if syspublications.immediate_sync = 1)
5) Run snapshot agent to generate snapshot for the new article, run
distribution agent to apply it
You probably know about this already but dropping subscription on an article
doesn't remove data that has already been replicated to the subscriber. Of
course, you should be able to manually drop the table easily at the
subscriber if that is what you want.
-Raymond
"SQL Replication Guy" <SQLReplicationGuy@.discussions.microsoft.com> wrote in
message news:CBE581C0-3897-4A40-A740-CB38D33D61C5@.microsoft.com...[vbcol=seagreen]
> Raymond thanks for the reply, all i ma trying to do is drop one table from
> existing publication and subscription.
> and if possible add another table to same publication and subscription...
> Regards,
> Ravi
> "Raymond Mak [MSFT]" wrote:
Dropping table Column in SQL server 6.5
I'm trying to drop a table column in SQL Server 6.5. I used the following
command and got error:
ALTER TABLE tablename
DROP COLUMN columnname
GO
It works in SQL Server 2000 version
Please I need help.
Thanks.
Ebon.
Hi,
You cant delete a column in sql 6.5
Only way is :-
1. put the data into a new table (select * into table_backup from
real_table)
2. script the table and dependant objetcs
3. change the table script with out the unwanted column
4. Insert into table from table_backup
5. create dependant objects , indexes..
Thanks
Hari
MCDBA
"Egbon" <vnjowusi@.gosps.com> wrote in message
news:OCNIa$CoEHA.1248@.TK2MSFTNGP09.phx.gbl...
> I'm trying to drop a table column in SQL Server 6.5. I used the following
> command and got error:
> ALTER TABLE tablename
> DROP COLUMN columnname
> GO
> It works in SQL Server 2000 version
> Please I need help.
> Thanks.
> Ebon.
>
|||Many thanks! Hari.
Egbon.
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:OtjNXHFoEHA.2588@.TK2MSFTNGP12.phx.gbl...[vbcol=seagreen]
> Hi,
> You cant delete a column in sql 6.5
> Only way is :-
> 1. put the data into a new table (select * into table_backup from
> real_table)
> 2. script the table and dependant objetcs
> 3. change the table script with out the unwanted column
> 4. Insert into table from table_backup
> 5. create dependant objects , indexes..
> Thanks
> Hari
> MCDBA
> "Egbon" <vnjowusi@.gosps.com> wrote in message
> news:OCNIa$CoEHA.1248@.TK2MSFTNGP09.phx.gbl...
following
>
command and got error:
ALTER TABLE tablename
DROP COLUMN columnname
GO
It works in SQL Server 2000 version
Please I need help.
Thanks.
Ebon.
Hi,
You cant delete a column in sql 6.5
Only way is :-
1. put the data into a new table (select * into table_backup from
real_table)
2. script the table and dependant objetcs
3. change the table script with out the unwanted column
4. Insert into table from table_backup
5. create dependant objects , indexes..
Thanks
Hari
MCDBA
"Egbon" <vnjowusi@.gosps.com> wrote in message
news:OCNIa$CoEHA.1248@.TK2MSFTNGP09.phx.gbl...
> I'm trying to drop a table column in SQL Server 6.5. I used the following
> command and got error:
> ALTER TABLE tablename
> DROP COLUMN columnname
> GO
> It works in SQL Server 2000 version
> Please I need help.
> Thanks.
> Ebon.
>
|||Many thanks! Hari.
Egbon.
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:OtjNXHFoEHA.2588@.TK2MSFTNGP12.phx.gbl...[vbcol=seagreen]
> Hi,
> You cant delete a column in sql 6.5
> Only way is :-
> 1. put the data into a new table (select * into table_backup from
> real_table)
> 2. script the table and dependant objetcs
> 3. change the table script with out the unwanted column
> 4. Insert into table from table_backup
> 5. create dependant objects , indexes..
> Thanks
> Hari
> MCDBA
> "Egbon" <vnjowusi@.gosps.com> wrote in message
> news:OCNIa$CoEHA.1248@.TK2MSFTNGP09.phx.gbl...
following
>
Dropping table Column in SQL server 6.5
I'm trying to drop a table column in SQL Server 6.5. I used the following
command and got error:
ALTER TABLE tablename
DROP COLUMN columnname
GO
It works in SQL Server 2000 version
Please I need help.
Thanks.
Ebon.Hi,
You cant delete a column in sql 6.5
Only way is :-
1. put the data into a new table (select * into table_backup from
real_table)
2. script the table and dependant objetcs
3. change the table script with out the unwanted column
4. Insert into table from table_backup
5. create dependant objects , indexes..
Thanks
Hari
MCDBA
"Egbon" <vnjowusi@.gosps.com> wrote in message
news:OCNIa$CoEHA.1248@.TK2MSFTNGP09.phx.gbl...
> I'm trying to drop a table column in SQL Server 6.5. I used the following
> command and got error:
> ALTER TABLE tablename
> DROP COLUMN columnname
> GO
> It works in SQL Server 2000 version
> Please I need help.
> Thanks.
> Ebon.
>|||Many thanks! Hari.
Egbon.
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:OtjNXHFoEHA.2588@.TK2MSFTNGP12.phx.gbl...
> Hi,
> You cant delete a column in sql 6.5
> Only way is :-
> 1. put the data into a new table (select * into table_backup from
> real_table)
> 2. script the table and dependant objetcs
> 3. change the table script with out the unwanted column
> 4. Insert into table from table_backup
> 5. create dependant objects , indexes..
> Thanks
> Hari
> MCDBA
> "Egbon" <vnjowusi@.gosps.com> wrote in message
> news:OCNIa$CoEHA.1248@.TK2MSFTNGP09.phx.gbl...
> > I'm trying to drop a table column in SQL Server 6.5. I used the
following
> > command and got error:
> >
> > ALTER TABLE tablename
> > DROP COLUMN columnname
> > GO
> >
> > It works in SQL Server 2000 version
> > Please I need help.
> >
> > Thanks.
> >
> > Ebon.
> >
> >
>
command and got error:
ALTER TABLE tablename
DROP COLUMN columnname
GO
It works in SQL Server 2000 version
Please I need help.
Thanks.
Ebon.Hi,
You cant delete a column in sql 6.5
Only way is :-
1. put the data into a new table (select * into table_backup from
real_table)
2. script the table and dependant objetcs
3. change the table script with out the unwanted column
4. Insert into table from table_backup
5. create dependant objects , indexes..
Thanks
Hari
MCDBA
"Egbon" <vnjowusi@.gosps.com> wrote in message
news:OCNIa$CoEHA.1248@.TK2MSFTNGP09.phx.gbl...
> I'm trying to drop a table column in SQL Server 6.5. I used the following
> command and got error:
> ALTER TABLE tablename
> DROP COLUMN columnname
> GO
> It works in SQL Server 2000 version
> Please I need help.
> Thanks.
> Ebon.
>|||Many thanks! Hari.
Egbon.
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:OtjNXHFoEHA.2588@.TK2MSFTNGP12.phx.gbl...
> Hi,
> You cant delete a column in sql 6.5
> Only way is :-
> 1. put the data into a new table (select * into table_backup from
> real_table)
> 2. script the table and dependant objetcs
> 3. change the table script with out the unwanted column
> 4. Insert into table from table_backup
> 5. create dependant objects , indexes..
> Thanks
> Hari
> MCDBA
> "Egbon" <vnjowusi@.gosps.com> wrote in message
> news:OCNIa$CoEHA.1248@.TK2MSFTNGP09.phx.gbl...
> > I'm trying to drop a table column in SQL Server 6.5. I used the
following
> > command and got error:
> >
> > ALTER TABLE tablename
> > DROP COLUMN columnname
> > GO
> >
> > It works in SQL Server 2000 version
> > Please I need help.
> >
> > Thanks.
> >
> > Ebon.
> >
> >
>
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
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
Subscribe to:
Posts (Atom)