End users in my company frequently use Microsoft Query (through Excel) to perform ad-hoc queries of selected SQL Server databases. As we move to SQL Server 2005 on the backend, we are discovering that MS Query lists all objects from all schemas, regardless of whether the user has permissions to those objects or schemas (they don't). I have also tried using the newer Native Client drivers (both 9.0 and 10.0) but received the same results.
I have found that the users can select the schema they want when they select the connection in Microsoft Query, but if possible, I'd like to hide the unnecessary system objects and schemas to prevent confusion. Has anyone discovered a way to do this?
Best Answer
Recently an article was published about this issue on Microsoft's KB: Microsoft Query lists user objects and system views in the query wizard.