I'm experiencing a weird problem with a SQL login. When I connect to the server in Microsoft SQL Server Management Studio (2008) using this account, I cannot see any of the tables, stored procedures etc. that this account should have access to on a particular database.
When I connect to the same server within Visual Studio (2008) with the same account everything is there. When I connect with the same account on a Virtual Machine everything is there. I've also had a co-worker connect to the server using the same login and he's able to view everything as well.
I use Microsoft SQL Server Management Studio all day connecting to different servers and databases and I've never experienced this problem.
Does anyone have any suggestions on how I can diagnose this problem?
I've checked to make sure I don't have any Table filters etc.
There's several database on this server and I'm able to see the correct tables that this account has access to in the other databases just fine.
Running this query lists the tables I'm expecting to see.
SELECT * FROM INFORMATION_SCHEMA.TABLES
I've solved my problem. It's a bug... Here are the details
This is a bug in SMO. We are planning to fix this by next major release which will be SQL11. This bug is actually not related to schemas at all but is related to the table owners. When we try to get the tables from the server in SMO, because of performance reasons we try to get their owners too in one go by doing an INNER JOIN. Hence, if the user doesn’t have permissions to view the table owners, he is not able to see the tables also using SMO which is a bug. Hence the workaround for this is, provide view permissions to the user on all the owners of the table. Another way will be to make this user “security admin” of the database but then this user will get lots of additional privileges, hence second solution is not recommended. Providing view permissions on table-owners to the user is a better way.