We plan to change the default server options of an SQL2k5 server instance by enabling data access.
The reason is that we want to run
SELECT * FROM OPENQUERY(LOCALSERVER, '...') - like statements on the server.
What are the possible disadvantages of enabling server option "data access" (alias
sys.servers.is_data_access_enabled) for the local server (
sys.servers.server_id = 0)?
(There must be a reason for MS setting this option to disabled by default...)
EDIT: it turns out that I'm not the first person to ask this question:
The DATA ACCESS server option is not very well documented in my opinion - the Books On Line say it is a property of linked servers. It doesn't mention at all that you actually can have it enabled on your local server to enable OPENQUERY calls. I noticed that when you disable DATA ACCESS on a linked server, you can't query any table located on it (I tested it on my loopback server) neither using OPENQUERY nor four-part naming convention. You can still call procedures (with four-part naming) that return rowsets. Well, the interesting question is why it is disabled by default on local server - I suppose to discourage users from using OPENQUERY against it.