Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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?

share|improve this question
    
It's the same story when using ODBC to interrogate an Access database via SQL :( –  Mark Henderson Jul 21 '09 at 21:21

5 Answers 5

up vote 2 down vote accepted

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

share|improve this answer
    
Outstanding! That article answers my question. Thank you for pointing it out! –  Ed Leighton-Dick Apr 29 '11 at 11:24

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.

share|improve this answer

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

share|improve this answer

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?

share|improve this answer
    
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 0: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

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.

share|improve this answer
    
Would be interested to see exactly which driver and version you're using, too, if it's working. –  squillman Jul 22 '09 at 1:55
    
SQL Server 2000 does hide the system objects - it's only SQL Server 2005 and 2008 that exhibit this behavior. –  Ed Leighton-Dick Jul 22 '09 at 14:27

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.