what's the Risk of "Ad Hoc Distributed Queries"

Question

I use an users to login SQLServer(not sa) so I can't exec the "exec sp_configure 'Ad Hoc Distributed Queries',1"

if always set the 'Ad Hoc Distributed Queries' on? is there any risk or disadvantage ?

Answer 1

Assuming you are going from SQL to SQL, IMO you should try and use sp_addlinkedserver instead - this way your DBA's have better visibility and control into cross server data access.

The only reason I could think why you would want to use OPENROWSET was if you wanted to directly access a non-SQL data store - but you might consider other technologies at application level which might be preferable, e.g. DTS / SSIS, or a custom application with 2 connection strings.

That said, it seems there is a bug with the Ad Hoc Queries option.