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?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.