0

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?

Ed Leighton-Dick
  • 1,094
  • 1
  • 7
  • 12

6 Answers6

2

Recently an article was published about this issue on Microsoft's KB: Microsoft Query lists user objects and system views in the query wizard.

warren
  • 17,829
  • 23
  • 82
  • 134
Suresh
  • 36
  • 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.

MileHigh
  • 21
  • 1
1

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

squillman
  • 37,618
  • 10
  • 90
  • 145
1

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?

mrdenny
  • 27,074
  • 4
  • 40
  • 68
  • I've denied select for public to the sys and INFORMATION_SCHEMA schemas to try and get around this to no avail... After tracing MS Query in Profiler I found that it does 3 calls to sp_tables at which point the MS Query UI drop-down is filled in with tables and views. I don't think this is so much of a thing with SQL permissions as it is the way MS Query is (crappily) built. – squillman Jul 22 '09 at 00:59
  • I've tried that, also. squillman's analysis fits what I see - it just dumps all of the objects, ignoring the permissions on the objects. – Ed Leighton-Dick Jul 22 '09 at 14:24
  • What login was used by MS Query to run sp_tables? MS Query can't bypass SQL Server security. If it could so could anything else just by saying that it was MS Query. I ask again, what objects can the user see in SSMS? I created a new database login in SQL with public access to master, msdb, tempdb, and a user database. I could see only those databases, and I could see no tables within those databases. It must be a security issues on the SQL Server. – mrdenny Jul 23 '09 at 14:41
  • This would seem to be a change in sp_tables itself. Here's a test I just performed: 1) Create a new login on the server and add it to an existing database, but just leave it in the public role. Don't give it any other permissions. 2) Login as that user. 3) Run EXEC sp_tables. On a SQL2000 box, sp_tables returns only the objects for the dbo user; on a SQL2005 box, it returns everything in the default schema plus sys and the information schema. – Ed Leighton-Dick Jul 24 '09 at 21:18
0

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.

Booji Boy
  • 295
  • 2
  • 5
  • 11
-3

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"

Sergio O
  • 1
  • The user actually states that he wants to hide system objects. Your solution is to mark his system objects as system objects (which is kind of funny). And DbDefence is a major case of `overkill` for the question. – Reaces Feb 17 '15 at 10:57