Home / server / Questions / 43751
Accepted
Ed Leighton-Dick
Asked: 2009-07-22 12:13:23 +0800 CST

Hiding SQL Server 2005 system objects/schemas from Microsoft Query

  • 772

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?

sql-server microsoft-excel

6 Answers

  1. 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.

    • 2

  2. http://support.microsoft.com/kb/2513216 Microsoft blames users? It is a known bug in SQL Server that Microsoft will not address. There are many warnings about deny of Select for the Public profile.

    • 2

  3. No, not that I've ever found. Microsoft Query does a call to [database].sys.sp_tables which in turn pulls from sys.all_objects and sys.all_columns in order to get the table / view information to present to the user. You can't deny execute to sp_tables (ok, you can but at the server level) so my conclusion to this one has always been "you're out of luck"...

    • 1

  4. Users should only be able to see objects which they have access to. It's part of the new security model of SQL Server 2005 and higher. Check that the users don't have rights to those objects through the public role. Can they see the objects via SQL Server Management Studio?

    • 1

  5. I agree. I've found that in Excel 2000/2003 (not sure about 2007) if you use a ODBC system DSN to access sql server 2000 the users can only see objects they have permissions to. I'll check SQL Server 2005 w/ Excel 2007 and update later.

    • 0

  6. DbDefence uses undocumented features to hide database schema even from DBA. Works in SQL Server 2005. They give away free version as well.

    If you just want to hide it slightly you can use "EXEC sys.sp_MS_marksystemobject"

    • -3