Sql-server – how to temporarily change the sql server settings in order to do a task and when finished revert back

configurationdistributed-queriesopenrowsetsp-configuresql server

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

Best Answer

I'm not sure if what you are building is a good idea or not but the code below should get it working.

You basically need to include the GO statement to separate the script into individual batches. Using a temporary table to store the original settings values allows access to the values by each of the batches.

You can't include the GO statement in a stored procedure so if you are looking to automate this process you will need to separate the script out another way (Agent job steps, SSIS, PowerShell, etc)

IF OBJECT_ID('tempdb.dbo.#Settings') IS NOT NULL
    DROP TABLE #Settings;

CREATE TABLE #Settings
(
    Setting VARCHAR(100),
    Val INT
)
INSERT #Settings (Setting, Val)
SELECT 'show advanced options', cast(value_in_use as int) from sys.configurations where name = 'show advanced options'
UNION
SELECT 'xp_cmdshell', cast(value_in_use as int) from sys.configurations where name = 'xp_cmdshell'
UNION
SELECT 'Ad Hoc Distributed Queries', cast(value_in_use as int) from sys.configurations where name = 'Ad Hoc Distributed Queries'

SELECT * FROM #Settings;


if ((SELECT val FROM #Settings where Setting = 'show advanced options') = 0)
begin
    exec sp_configure 'show advanced options', 1
    reconfigure
end

if ((SELECT val FROM #Settings where Setting = 'xp_cmdshell') = 0)
begin
    exec sp_configure 'xp_cmdshell', 1
    reconfigure
end

if ((SELECT val FROM #Settings where Setting = 'Ad Hoc Distributed Queries')  = 0)
begin
    exec sp_configure 'Ad Hoc Distributed Queries', 1
    reconfigure
end
go
/* -----------------------------------------------------------------  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
---------------------------------------------------
go
if ((SELECT val FROM #Settings where Setting = 'xp_cmdshell') = 0)
begin
    exec sp_configure 'xp_cmdshell', 0
    reconfigure
end

if ((SELECT val FROM #Settings where Setting = 'Ad Hoc Distributed Queries') = 0)
begin
    exec sp_configure 'Ad Hoc Distributed Queries', 0
    reconfigure
end

if ((SELECT val FROM #Settings where Setting = 'show advanced options') = 0)
begin
    exec sp_configure 'show advanced options', 0
    reconfigure
end

IF OBJECT_ID('tempdb.dbo.#Settings') IS NOT NULL
    DROP TABLE #Settings;
Related Question