Disaster Recovery

Whilst importing some procedures, I have left the
'Transfer Dependent Object' checked and the 'Drop Destination Objects'
result I have over-written certain tables
Can it be undone with the
transaction log and if so How ?
regards EarnieIf you have a backup of your target database, prior to the overwrite,
restore it. Or restore it with a different name, and copy the objects you
need.
In case of no backup, you can use a tool like Lumigent Log Explorer to undo
those changes.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Earnie" <Earnie@.discussions.microsoft.com> wrote in message
news:EADFAF0B-FD14-4937-9F6D-98DFAE297CFF@.microsoft.com...
Whilst importing some procedures, I have left the
'Transfer Dependent Object' checked and the 'Drop Destination Objects'
result I have over-written certain tables
Can it be undone with the
transaction log and if so How ?
regards Earnie|||Assuming you have valid database and log backups, use the STOPAT option
to restore the log to a point in time before the tables were droppped.
David Portas
SQL Server MVP
--

Disappearing Linked Server Stored Procedures

When I create a stored procedure like the one below and save it,
everything seems fine. But when I close Enterprise Manager and
re-open it, the stored procedure is gone.

Any ideas?

SET ANSI_DEFAULTS ON
GO
CREATE PROCEDURE [dbo].[sp_Test] AS
SELECT * FROM OPENQUERY(LINKEDSERVER, 'SELECT V.VENDOR_ID,
V.VENDOR_NAME FROM VENDOR V')
GO

Ted"Ted Calhoon" <tedcalhoon@.hotmail.com> wrote in message
news:fc57c4d8.0312121455.1e0677d8@.posting.google.c om...
> When I create a stored procedure like the one below and save it,
> everything seems fine. But when I close Enterprise Manager and
> re-open it, the stored procedure is gone.
> Any ideas?
>
> SET ANSI_DEFAULTS ON
> GO
> CREATE PROCEDURE [dbo].[sp_Test] AS
> SELECT * FROM OPENQUERY(LINKEDSERVER, 'SELECT V.VENDOR_ID,
> V.VENDOR_NAME FROM VENDOR V')
> GO
>
> Ted

If you put only the CREATE PROCEDURE statement into the create procedure
dialogue, it will work:

CREATE PROCEDURE [dbo].[sp_Test] AS
SELECT * FROM OPENQUERY(LINKEDSERVER, 'SELECT V.VENDOR_ID,
V.VENDOR_NAME FROM VENDOR V')

Presumably, this is a 'feature' in EM, which seems to have a number of
quirks. If you need full control over the CREATE PROC statement, you should
execute it in Query Analyzer instead, which is probably a better practice in
general.

Also, don't use sp_ as a procedure name prefix, that's reserved for system
stored procedures.

Simon|||Thanks, Simon. I will take your advice and:

1. Use Query Analyzer to create stored procedures
2. Avoid using sp_ when naming my stored procedures

Ted

Disabling triggers from stored procedures

Hi,
Is it possible to disable a trigger from a Stored Procedure? If it is, how do you do it?
Thanks,
FedericoALTER TABLE [MyTable] DISABLE TRIGGER trMyTrigger|||Just curious as to why you would want to do this?|||Yes, it is possible to disable the trigger, but keep in mind that you are disabling that trigger for everyone using the table, not just for what the trigger is doing. I don't know of any way to allow a trigger to function for some users/spids and not for others, except to include code within the trigger to make it decide that it shouldn't run under some conditions.

-PatP|||If SQL Server 2000 and one can alter the trigger then one can use SET CONTEXT_INFO in SP and then check master..sysprocesses from trigger.

Might also insert DBCC INPUTBUFFER(@.@.SPID) results into temporary table in trigger to check for proc name in EventInfo column. Would work in SQL Server 7 and would not require any additional code in SP, but would hurt trigger performance, be kludgy, and require hard-coding proc name(s) in trigger.

Disabling extended stored procedures for security

Our security team wants us to disable access to (or drop) all of the built-in extended stored procedures in MSDE 2000 as they feel it is a vulnerability. Where can I find out which extended procs are safe to disable or how we can disable them during install time? Or, is the security team being too cautious and we should just tell them to leave these intact?

There are some that are extremely useful (perhaps even necessary) for many routine maintenance tasks.

Perhaps your security department just needs to know that you have access to the stored procedures 'locked' down to only administrative users.

|||

The onyl procedure which is questionable to let it enabled it the xp_cmdshell procedure as it can execute a command shell on the server. But... if noone (or only adminstrators) are granted access to this procedure there should be no security impact for you at all. make just sure that noone create a extended stored procedure which is not shipped by MS.

Code Snippet

SELECT

xproc.name AS [Name],

SCHEMA_NAME(xproc.schema_id)AS [Schema],

CAST(

xproc.is_ms_shipped

ASbit)AS [IsSystemObject],

xproc.create_date AS [CreateDate]

FROM

sys.all_objectsAS xproc

WHERE

(xproc.type='X')and(CAST(

xproc.is_ms_shipped

ASbit)=0)

ORDERBY

[Schema] ASC,[Name] ASC

Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Thanks Jens. The security dept wants us to drop xp_cmdshell, not just remove the rights. Our MSDE 2000 database stores app settings and is not involved in heavy transactions or replication, etc. So would dropping this cause any harm? I read in the Microsoft KB article that it could affect some other stored procedures that depend on it, but those SPs appear to support enterprise-level features.

|||

I agree with Jens. I would not remove xp_cmdshell since I find it very useful for automating maintenance tasks.

However, set the permissions so that only the sysadmin role can execute xp_cmdshell. (Unless, of course, the worst imaginable sin has been commited and the application is running under the 'sa' account.)

No one, no group, no other role, except the very small list of SQL Server administrators 'should' be in the sysadmin role -AND the sa account 'should' NOT be used at all, and it should be tightly locked down with a strong password.

Since the SQL Server administrators have access to the server, there is nothing that they could do using xp_cmdshell that they couldn't do at an OS level command prompt.

Completely removing xp_cmdshell is folly. Tightly limiting access through appropriate permissions is a 'best practice'.

But if you MUST remove it, there 'may' be little impact. Only time will tell. And you can always put it back if necessary.

Disabling extended stored procedures for security

Our security team wants us to disable access to (or drop) all of the built-in extended stored procedures in MSDE 2000 as they feel it is a vulnerability. Where can I find out which extended procs are safe to disable or how we can disable them during install time? Or, is the security team being too cautious and we should just tell them to leave these intact?

There are some that are extremely useful (perhaps even necessary) for many routine maintenance tasks.

Perhaps your security department just needs to know that you have access to the stored procedures 'locked' down to only administrative users.

|||

The onyl procedure which is questionable to let it enabled it the xp_cmdshell procedure as it can execute a command shell on the server. But... if noone (or only adminstrators) are granted access to this procedure there should be no security impact for you at all. make just sure that noone create a extended stored procedure which is not shipped by MS.

Code Snippet

SELECT

xproc.name AS [Name],

SCHEMA_NAME(xproc.schema_id) AS [Schema],

CAST(

xproc.is_ms_shipped

AS bit) AS [IsSystemObject],

xproc.create_date AS [CreateDate]

FROM

sys.all_objects AS xproc

WHERE

(xproc.type='X')and(CAST(

xproc.is_ms_shipped

AS bit)=0)

ORDER BY

[Schema] ASC,[Name] ASC

Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Thanks Jens. The security dept wants us to drop xp_cmdshell, not just remove the rights. Our MSDE 2000 database stores app settings and is not involved in heavy transactions or replication, etc. So would dropping this cause any harm? I read in the Microsoft KB article that it could affect some other stored procedures that depend on it, but those SPs appear to support enterprise-level features.

|||

I agree with Jens. I would not remove xp_cmdshell since I find it very useful for automating maintenance tasks.

However, set the permissions so that only the sysadmin role can execute xp_cmdshell. (Unless, of course, the worst imaginable sin has been commited and the application is running under the 'sa' account.)

No one, no group, no other role, except the very small list of SQL Server administrators 'should' be in the sysadmin role -AND the sa account 'should' NOT be used at all, and it should be tightly locked down with a strong password.

Since the SQL Server administrators have access to the server, there is nothing that they could do using xp_cmdshell that they couldn't do at an OS level command prompt.

Completely removing xp_cmdshell is folly. Tightly limiting access through appropriate permissions is a 'best practice'.

But if you MUST remove it, there 'may' be little impact. Only time will tell. And you can always put it back if necessary.

Disabled sp_oacreate in sql 2000?

Hi all,
I have an issue where a SQL server (2000 SP3a) is not executing
external objects. I have granted permissions to all sp_oa* stored
procedures as well as xp_cmdshell; I also ensured read/execute access
is enabled for Everyone on wscript.exe/dll. I am running the following
as 'sa':
DECLARE @.pid int, @.hr int, @.path varchar(500)
SET @.path = 'd:\public\\script\job.wsf'
EXEC @.hr = sp_OACreate 'WScript.Shell', @.pid OUT
EXEC @.hr = sp_OAMethod @.pid, 'Run', NULL, @.path
EXEC @.hr = sp_OADestroy @.pid
Command completes successfully, but the script never executes. I
have tryed with other objects and same result. I can see in Profiler
the script executes fine, but Windows Script Host never gets invoked.
This works on one server but not another; a third party hosting
facility installed the OS and DB but I'm not sure what they have
disabled, etc. or how to enable this. I know they've played with
security policies and registry settings but don't know what fixes this
issue.
Any help is greatly appreciated. Have a super day!
> Rich NorgaardDoes it work when you are logged in with sysadmin permissions? I'm thinking the proxy account
config...
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Rich" <richnorgaard@.hotmail.com> wrote in message
news:447699bb.0310151115.268b1c5c@.posting.google.com...
> Hi all,
> I have an issue where a SQL server (2000 SP3a) is not executing
> external objects. I have granted permissions to all sp_oa* stored
> procedures as well as xp_cmdshell; I also ensured read/execute access
> is enabled for Everyone on wscript.exe/dll. I am running the following
> as 'sa':
> DECLARE @.pid int, @.hr int, @.path varchar(500)
> SET @.path = 'd:\public\\script\job.wsf'
> EXEC @.hr = sp_OACreate 'WScript.Shell', @.pid OUT
> EXEC @.hr = sp_OAMethod @.pid, 'Run', NULL, @.path
> EXEC @.hr = sp_OADestroy @.pid
> Command completes successfully, but the script never executes. I
> have tryed with other objects and same result. I can see in Profiler
> the script executes fine, but Windows Script Host never gets invoked.
> This works on one server but not another; a third party hosting
> facility installed the OS and DB but I'm not sure what they have
> disabled, etc. or how to enable this. I know they've played with
> security policies and registry settings but don't know what fixes this
> issue.
> Any help is greatly appreciated. Have a super day!
> > Rich Norgaard

Disable/Enable Triggers

I have stored procedure that disable triggers from another table then
completes the
body for the stored procedures then enables the triggers.
Is there an easy way to complete this task of disable and enable triggers
with very little overhead.
Thank You,
On Thu, 3 Nov 2005 14:30:03 -0800, Joe K. <Joe
K.@.discussions.microsoft.com> wrote:

>I have stored procedure that disable triggers from another table then
>completes the
>body for the stored procedures then enables the triggers.
>Is there an easy way to complete this task of disable and enable triggers
>with very little overhead.
>Thank You,
Hi Joe,
The good news: look up
ALTER TABLE table
{ ENABLE | DISABLE } TRIGGER { ALL | trigger_name [,..n] }
in Books Online.
The bad news: this is not a per-connection setting. If you disable the
trigger, it won't fire for ANY connection that's accessing the table.
Unless you can be sure that the stored proc is the only active process
at the time, I'd urge you to find another way to achieve what you want.
If you post more information about what business problem you're trying
to solve, we can help you find a better way.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)

Disable/Enable Triggers

I have stored procedure that disable triggers from another table then
completes the
body for the stored procedures then enables the triggers.
Is there an easy way to complete this task of disable and enable triggers
with very little overhead.
Thank You,On Thu, 3 Nov 2005 14:30:03 -0800, Joe K. <Joe
K.@.discussions.microsoft.com> wrote:
>I have stored procedure that disable triggers from another table then
>completes the
>body for the stored procedures then enables the triggers.
>Is there an easy way to complete this task of disable and enable triggers
>with very little overhead.
>Thank You,
Hi Joe,
The good news: look up
ALTER TABLE table
{ ENABLE | DISABLE } TRIGGER { ALL | trigger_name [,..n] }
in Books Online.
The bad news: this is not a per-connection setting. If you disable the
trigger, it won't fire for ANY connection that's accessing the table.
Unless you can be sure that the stored proc is the only active process
at the time, I'd urge you to find another way to achieve what you want.
If you post more information about what business problem you're trying
to solve, we can help you find a better way.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Disable/Enable Triggers

I have stored procedure that disable triggers from another table then
completes the
body for the stored procedures then enables the triggers.
Is there an easy way to complete this task of disable and enable triggers
with very little overhead.
Thank You,On Thu, 3 Nov 2005 14:30:03 -0800, Joe K. <Joe
K.@.discussions.microsoft.com> wrote:

>I have stored procedure that disable triggers from another table then
>completes the
>body for the stored procedures then enables the triggers.
>Is there an easy way to complete this task of disable and enable triggers
>with very little overhead.
>Thank You,
Hi Joe,
The good news: look up
ALTER TABLE table
{ ENABLE | DISABLE } TRIGGER { ALL | trigger_name [,..n] }
in Books Online.
The bad news: this is not a per-connection setting. If you disable the
trigger, it won't fire for ANY connection that's accessing the table.
Unless you can be sure that the stored proc is the only active process
at the time, I'd urge you to find another way to achieve what you want.
If you post more information about what business problem you're trying
to solve, we can help you find a better way.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

disable recompile

Sometimes our stored procedures recompile and they get a poor query plan.
How can one stop the stored procs from recompiling ?
Possibly investigate the KEEP FIXEDPLAN optimizer hint. But read
http://msdn.microsoft.com/library/en...ompilation.asp and
http://www.microsoft.com/technet/pro...05/recomp.mspx first.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Just Me" <justme@.hotmail.com> wrote in message news:e3PdX787FHA.720@.TK2MSFTNGP10.phx.gbl...
> Sometimes our stored procedures recompile and they get a poor query plan.
> How can one stop the stored procs from recompiling ?
>

disable recompile

Sometimes our stored procedures recompile and they get a poor query plan.
How can one stop the stored procs from recompiling ?Possibly investigate the KEEP FIXEDPLAN optimizer hint. But read
http://msdn.microsoft.com/library/e...005/recomp.mspx first.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Just Me" <justme@.hotmail.com> wrote in message news:e3PdX787FHA.720@.TK2MSFTNGP10.phx.gbl...

> Sometimes our stored procedures recompile and they get a poor query plan.
> How can one stop the stored procs from recompiling ?
>

disable recompile

Sometimes our stored procedures recompile and they get a poor query plan.
How can one stop the stored procs from recompiling ?Possibly investigate the KEEP FIXEDPLAN optimizer hint. But read
http://msdn.microsoft.com/library/en-us/dnsql2k/html/sql_queryrecompilation.asp and
http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx first.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Just Me" <justme@.hotmail.com> wrote in message news:e3PdX787FHA.720@.TK2MSFTNGP10.phx.gbl...
> Sometimes our stored procedures recompile and they get a poor query plan.
> How can one stop the stored procs from recompiling ?
>