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?