SQL 2008 Management Studio does not display non-default schema objects

Question

SSMS2008 only lists default schema objects when connecting to SQL2005 or SQL 2008 servers.  The workaround is to grant view definition privilege to the user account or alter the schema and set authorization to the default schema. 

However, SSMS 2005 is working just fine (list all objects with all schemas) when connecting to both sql 2005 and sql2008 servers without making any change to the view definition.

I would like to know if this behavior in SSMS2008 is by designed, or a bug. 

Thanks!

Answer 1

Hi Yang,

Your workaround is right .
But recently we have other forum thread discussion about the same issue http://social.msdn.microsoft.com/Forums/en-US/sqltools/thread/a6166b82-1e1a-4771-952d-a7b54e0b0f23 .

---

Thanks, Leks

Answer 2

Hello Yang,

I did a test, if even I set the default schema to the user, but not give the VIEW DEFINITION on the schema to it, then I can not see the objects of that default schema either.

So as to repro your problem, could you tell us the detailed steps.

---

ChunSong Feng
Microsoft Online Community Support

Please remember to mark the replies as answers if they help and unmark them if they provide no help.
Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.

Answer 3

Thanks for your reply ChunSong! 

I am working with the existing databases, user accounts for the databases have minimum of db_datareader permission.  In that case, they could view the default schema objects from SSMS2008. 

My question is, if this is the new feature in SSMS2008?  However, I couldn't find any document published by MS on this.  It does not look like a sql server level issue, because SSMS2005 works fine for both SQL2005 and SQL2008 sql instances.  If it's a bug, how soon it can be fixed?


Thanks again!

Answer 4

I am working with the existing databases, user accounts for the databases have minimum of db_datareader permission.  In that case, they could view the default schema objects from SSMS2008. 

My question is, if this is the new feature in SSMS2008?  However, I couldn't find any document published by MS on this.  It does not look like a sql server level issue, because SSMS2005 works fine for both SQL2005 and SQL2008 sql instances.  If it's a bug, how soon it can be fixed?

Hello RLYang,

I still cannot repro your problem, below are what I did:

 

1       In my test database, I created a SQL user, and mapped the user to a SQL login.

2       Set the user default schema, but don’t grant the VIEW DEFINITION on the schema to the user.

3       Login the server using that login in SSMS 2005, it cannot view objects of the default schema.

4       I grant the db_datareader database fixed role to the user, login the server in SSMS 2008, it still cannot view the objects of the default schema.

 

So, I do not think it is a feature of SSMS 2008, is there any implicit permission granted to the user? Please check that and let me know.

---

ChunSong Feng
Microsoft Online Community Support

Please remember to mark the replies as answers if they help and unmark them if they provide no help.
Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.