Microsoft – SQL Server – Management Studio – v2008 – Database Objects not showing up

We had a bit of problem in Microsoft SQL Server – Management Studio – v2008.  

 

Though we had granted read permissions to one of our users via:

 

   create user [lab\myGoodUser] from login [lab\myGoodUser];
   exec sp_addrolemember [db_datareader], [lab\myGoodUser];

But, still the user upon logging on could not see any of the database objects. Looked everywhere, but no luck. So what to do:

  1. Kicked off “Microsoft SQL Server Profiler”
  2. Filtered for that user
  3. And, captured the sql generated when we refreshed the database object list

 

The Query we captured looks like:

 

 

SELECT

‘Server[@Name=’ + quotename(CAST(serverproperty(N’Servername’) AS sysname),””) + ‘]’ + ‘/Database[@Name=’ + quotename(db_name(),””) + ‘]’ + ‘/Table[@Name=’ + quotename(tbl.name,””) + ‘ and @Schema=’ + quotename(SCHEMA_NAME(tbl.schema_id),””) + ‘]’ AS [Urn],

tbl.name AS [Name],

SCHEMA_NAME(tbl.schema_id) AS [Schema],

CAST(

case

    when tbl.is_ms_shipped = 1 then 1

    when (

        select

            major_id

        from

            sys.extended_properties

        where

            major_id = tbl.object_id and

            minor_id = 0 and

            class = 1 and

            name = N’microsoft_database_tools_support’)

        is not null then 1

    else 0

end         
             AS bit) AS [IsSystemObject],

tbl.create_date AS [CreateDate]

FROM

sys.tables AS tbl

WHERE

(CAST(

case

    when tbl.is_ms_shipped = 1 then 1

    when (

        select

            major_id

        from

            sys.extended_properties

        where

            major_id = tbl.object_id and

            minor_id = 0 and

            class = 1 and

            name = N’microsoft_database_tools_support’)

        is not null then 1

    else 0

end         
             AS bit)=0)

ORDER BY

[Schema] ASC,[Name] ASC

 

 

Tried to understand the query, but it was not as easy. So what to do – Take the Easy way – Google….

 

SQL SERVER Management Studio 2008 can’t list all tables under SQL2005 database

http://www.sqlservercentral.com/Forums/Topic882092-391-4.aspx

 

In a nutshell:

 

  1. It seems that Management SQL Server Management Studio v2008 has problems when connecting to a v2005 database Instance
  2. The problem can be fixed by granting access to ‘view definition’

 

 

To grant access to ‘View Definition’

 

 

1) Grant ‘View Definition’ to specific object

–grant view definition on all database objects

use [msdb]

GRANT VIEW DEFINITION ON object::[dbo].[DTA_input] TO [lab\mygoodUser];

 

 

2) Grant ‘View Definition’ to specific schemas

use [myDB];

http://msdn.microsoft.com/en-us/library/ms187940.aspx

–grant ability to view schema definition

–GRANT VIEW DEFINITION ON SCHEMA::<schema> TO [login];

GRANT VIEW DEFINITION ON SCHEMA::dbo TO [lab\myGoodUser];

 

 

3) Grant ‘View Definition’ to specific database

http://msdn.microsoft.com/en-us/library/ms173848.aspx

–grant ability to view database definition

–GRANT VIEW DEFINITION ON DATABASE::<database> TO [login];

GRANT VIEW DEFINITION ON DATABASE::[myDB] TO [lab\mygoodUser];

 

4) Grant ‘View Definition’ to current database

–GRANT VIEW DEFINITION TO [login];

GRANT VIEW DEFINITION TO [lab\mygoodUser];

 

5) Grant ‘View Definition’ on all databases

–grant view definition on all database objects

GRANT VIEW ANY DEFINITION TO [lab\mygoodUser];


 

The problem appears to be with sys.tables. 

 

Data returned from this table is governed based on user’s permission granted via ‘view definition’.

To validate issue query ( select * form sys.tables) against sys.tables and results will vary depending on permissions.

The error will not be a hard-error \ fault, but a bit more subtle.

 

More data:

 

  1. The problem is confirmed in MS SQL Server v2005 Engine when accessed via MS SQL Server – v2008 – Management Studio
  2. There is a MS Connect Article that is created:

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

    https://connect.microsoft.com/SQLServer/feedback/details/387616/tables-node-does-not-show-all-schemas-in-ssmse-2008

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s