2

Included in the code of the creation of some stored procedures, I have the enabling of data access as you can see below.

BEGIN TRY
    PRINT  @@SERVERNAME
    PRINT  DB_NAME()
    PRINT 'enable the data access for the current server'
    EXEC sp_serveroption @server = @@servername
                        ,@optname =  'data access'     
                        ,@optvalue =  'TRUE'
END TRY
BEGIN CATCH
        PRINT '--EXCEPTION WAS CAUGHT--' + CHAR(13) +
              'THE ERROR NUMBER:' + COALESCE(CAST ( ERROR_NUMBER()  AS VARCHAR), 'NO INFO') + CHAR(13) 

        PRINT 'SEVERITY: '        + COALESCE(CAST ( ERROR_SEVERITY()  AS VARCHAR), 'NO INFO') + CHAR(13) +
              'STATE: '           + COALESCE(CAST ( ERROR_STATE() AS VARCHAR), 'NO INFO')  + CHAR(13) 

        PRINT 'PROCEDURE: '       + COALESCE(CAST ( COALESCE(ERROR_PROCEDURE(),'NO INFO')  AS VARCHAR), 'NO INFO') + CHAR(13) +
              'LINE NUMBER: '     + COALESCE(CAST ( ERROR_LINE() AS VARCHAR), 'NO INFO')  + CHAR(13) 

        PRINT 'ERROR MESSAGE: '
        PRINT  CAST ( COALESCE(ERROR_MESSAGE(),'NO INFO')   AS NTEXT)

END CATCH
GO 

I find this handy when I know that I will be using the stored procedures through a call to OPENQUERY as shown on the example code below.

            declare @sql nvarchar(max)
            select @sql = 'SELECT * FROM OPENQUERY(' + quotename(@@servername) +
 ',' + '''' + 
'SET FMTONLY OFF; exec sp_GetServerLogins ' + '''' + ')'

            INSERT INTO #Radhe
            exec ( @sql)

            SELECT * FROM #Radhe

This has been working fine for me, however, I am wondering of any possible security breach that I might open when I enable the data access.

Is it a good practice enabling data access this way? Any reason why I should not do it?

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.