Tables Node Does Not Show All Schemas In SSMSE 2008 - by Nicholas Piasecki

Status : 

  Fixed<br /><br />
		This item has been fixed in the current or upcoming version of this product.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.


7
0
Sign in
to vote
ID 387616 Comments
Status Closed Workarounds
Type Bug Repros 4
Opened 12/11/2008 6:29:45 AM
Access Restriction Public

Description

Let's say that we have a SQL Server Express 2005 database with three schemas (dbo, SchemaA, and SchemaB), a SQL Server login named "UserA" that is mapped to a database user of the same name "UserA" and belonging to roles "public", "db_datareader", and "db_datawriter".

If I use SSMSE 2005 to connect to the database as this user, I can see tables from all three schemas when I expand the "Tables" node in Object Explorer.

But if I use SSMSE 2008 to connect to the same database as this same user, I can only see tables from the dbo schema when I expand the "Tables" node in Object Explorer.

When executing "SELECT * FROM sys.tables" when connected as the user in either program, I can see results from all three schemas.
Sign in to post a comment.
Posted by EddyW on 2/15/2011 at 12:47 PM
Well, Granting VIEW DEFINITION on a schema to a user had not worked for us because grant to database role and found NO CHANGE in this behaviour. Even when granting to Public!

i.e.
GRANT VIEW DEFINITION ON SCHEMA::TEST TO Public;

So, do we grant SPECIFICALLY to the user?
If so, we define users using Active Directory Groups mapped to Database Roles. Highly considered a Best Practice.

Please clarify what you mean by "view permissions" is this the same as GRANT VIEW DEFINITION...

Thanks.
Eddy
Posted by Microsoft on 10/6/2010 at 5:31 AM
Hi,

We are considering to fix this issue in our Next Major Release which is presently planned to be released in 2011.

Regards,
Sumesh
MS SQL Server Team
Posted by Microsoft on 4/28/2009 at 11:13 PM
Hi,
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.

Regards,
Sumesh

Posted by BrentOfenstein on 4/16/2009 at 1:48 PM
I'm experiencing this same issue as we are starting our migration to SQL Server 2008 from 2005. We have started migrating our developers to SSMS 2008, and now they have this issue. I realize there is a workaround, but it's one more thing we have to configure for every person that is granted access to SQL Server. We have hundreds of users. Any word on when this will be addressed?