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?

Improve this question
1
  • It's the same story when using ODBC to interrogate an Access database via SQL :( Commented Jul 21, 2009 at 21:21

6 Answers 6

Reset to default
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.

Improve this answer
1
  • Outstanding! That article answers my question. Thank you for pointing it out! Commented Apr 29, 2011 at 11:24
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.

Improve this answer
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"...

Improve this answer
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?

Improve this answer
4
  • 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. Commented Jul 22, 2009 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. Commented Jul 22, 2009 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. Commented Jul 23, 2009 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. Commented Jul 24, 2009 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.

Improve this answer
2
  • Would be interested to see exactly which driver and version you're using, too, if it's working. Commented Jul 22, 2009 at 1:55
  • SQL Server 2000 does hide the system objects - it's only SQL Server 2005 and 2008 that exhibit this behavior. Commented Jul 22, 2009 at 14:27
-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"

Improve this answer
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. Commented Feb 17, 2015 at 10:57

You must log in to answer this question.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.