how to temporarily change the sql server settings in order to do a task and when finished revert back?
2
votes
1
answer
1493
views
when I run the script below all at once I get this error message:
> Msg 15281, Level 16, State 1, Line 58 SQL Server blocked access to
> STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed
> Queries' because this component is turned off as part of the security
> configuration for this server. A system administrator can enable the
> use of 'Ad Hoc Distributed Queries' by using sp_configure. For more
> information about enabling 'Ad Hoc Distributed Queries', search for
> 'Ad Hoc Distributed Queries' in SQL Server Books Online.
But when I run this first and then run the full script then it is all fine.
exec sp_configure 'Ad Hoc Distributed Queries', 1
reconfigure
The impression that I got is that sql-server will look at the configured values in memory, and does not seem to realise that they have changed.
How can I alter this behaviour?
here is the full script:
SET NOCOUNT ON;
declare @prevAdvancedOptions int
declare @prevXpCmdshell int
declare @adhocdistque int
-------------------------------------------------------------------------------------------
--SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries'
-- because this component is turned off as part of the security configuration for this server.
--A system administrator can enable the use of 'Ad Hoc Distributed Queries' by using sp_configure.
--For more information about enabling 'Ad Hoc Distributed Queries', see "Surface Area Configuration" in SQL Server Books Online.
select @prevAdvancedOptions = cast(value_in_use as int) from sys.configurations where name = 'show advanced options'
select @prevXpCmdshell = cast(value_in_use as int) from sys.configurations where name = 'xp_cmdshell'
select @adhocdistque = cast(value_in_use as int) from sys.configurations where name = 'Ad Hoc Distributed Queries'
PRINT @prevAdvancedOptions
PRINT @prevXpCmdshell
print @adhocdistque
if (@prevAdvancedOptions = 0)
begin
exec sp_configure 'show advanced options', 1
reconfigure
end
if (@prevXpCmdshell = 0)
begin
exec sp_configure 'xp_cmdshell', 1
reconfigure
end
if (@adhocdistque = 0)
begin
exec sp_configure 'Ad Hoc Distributed Queries', 1
reconfigure
end
/* ----------------------------------------------------------------- do work - begin */
SELECT *
FROM OPENROWSET('sqloledb', 'server=(local);trusted_connection=yes'
, 'set fmtonly off exec msdb.dbo.sp_help_job')
--where name in
-- ( 'WebFeed UKProductOffer Offers'
-- ,'WebFeed USProductOffer Offers'
-- ,'WebFeed DEProductOffer Offers'
-- ,'WebFeed ATProductOffer Offers'
-- ,'WebFeed FRProductOffer Offers'
-- ,'WebFeed EUProductOffer Offers'
-- ,'WebFeed AUProductOffer Offers')
/* ----------------------------------------------------------------- do work - end */
---------------------------------------------------
-- restore the settings as they were previously
---------------------------------------------------
if (@prevXpCmdshell = 0)
begin
exec sp_configure 'xp_cmdshell', 0
reconfigure
end
if (@prevAdvancedOptions = 0)
begin
exec sp_configure 'show advanced options', 0
reconfigure
end
if (@adhocdistque = 0)
begin
exec sp_configure 'Ad Hoc Distributed Queries', 0
reconfigure
end
Asked by Marcello Miorelli
(17274 rep)
Sep 28, 2016, 11:15 AM
Last activity: Sep 4, 2017, 04:10 PM
Last activity: Sep 4, 2017, 04:10 PM