none
SQL Management Studio 2008, does not display tables with specific permissions

    Question

  • Prev Reference Question: http://social.msdn.microsoft.com/Forums/en-US/sqltools/thread/a6166b82-1e1a-4771-952d-a7b54e0b0f23?prof=required

    I see the same problem with SQL 2008 Management Studio. I have different schema defined in my database, all the schema permissions work just fine in SQL 2005 but when viewed from SQL 2008. I cannot see tables for which i have given inidividual permissions. I have given SELECT permissions for those tables.

    for example.

    schema: dbo, schema1, schema2 ....

    user: user1 (schema1 owner), user2 (schema2 owner), user3 ...

    now i have given SELECT on schema1.table1 to user2

    this works fine and i m able to see the schema1.table1 when login as user2 in SQL2005. I have tried your solution for giving "View Definition" permission as well. But this doesnt work in my case.

    Seems to be some default permission settings with SQL 2008 ??

    Any help/Suggestion will be appreciated

    • Edited by kumar754 Friday, April 09, 2010 4:06 PM added reference to similar question
    Friday, April 09, 2010 4:05 PM

Answers

  • I'm running into the same issue with SSMS 2008: I cannot see in the Object Explorer tables from other schema on which I have SELECT access. I can interact with them from SQL, but not from Object Explorer.

     

    I stumbled across a workaround this evening - but I'm still not sure why it works. On my machine, I can:

     

    GRANT VIEW DEFINITION TO <user>

    GRANT VIEW SERVER STATE TO <user>

     

    If I revoke either privilege, the tables disappear from the Object Explorer tree upon refresh.

     

    Q: Is this by design in SQL Server 2008 - and, if so, why?

    • Marked as answer by kumar754 Wednesday, August 04, 2010 4:25 AM
    Saturday, July 24, 2010 4:56 AM

All replies

  • Regarding permissions in general, including VIEW DEFINITON, the following article points out a general feature of SQL Server security.  A security DENY will always override a GRANT.

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

    Therefore, a lower level DENY (for example, to a table) will override a high-level GRANT such as at the database level.  Of course, to understand whether this is happening, you would need to see all relevant permissions.  The following query will help:

    SELECT u.name, p.permission_name, p.class_desc, 
         OBJECT_NAME(p.major_id) ObjectName, state_desc 
    FROM sys.database_permissions  p join sys.database_principals u
        ON p.grantee_principal_id = u.principal_id
    ORDER BY p.permission_name, ObjectName, u.name

    Look for any DENY permissions and see if they conflict with your GRANT permissions.  If so, you can REVOKE the permission, which sets it back to neutral so that an existing GRANT will apply.

    RLF

    Friday, April 09, 2010 6:35 PM
  • I have the same problem. By default,  Management Studio only display  "dbo" schema, not any other schema which the users have read permission .    I do not think it is permission.   I can select the tables using sql .  What permission should I give to allow non dbo schema displayed in users' Management Studio?

    Also I found I have to give the users public role in msdb to allow them to see non-system databases in Management Studio Console, just read permission in user databases is not enough.

    Thursday, May 13, 2010 5:30 AM
  • There are a couple of permissions that you should look into:

    VIEW ANY DATABASE allows a grantee rights to metadata about all databases, regardless of right.

    VIEW ANY DEFINITION at server scope or VIEW DEFINITION at database, schema, entity scopes.

    RLF

    Thursday, May 13, 2010 1:39 PM
  • When I used SQL2005 Management Studio to connect to SQL2008 database, I can see all the schemas and user databases .


    When I used SQL2008 Management Studio to connect to the same SQL2008 database with same login , I can not see anything under databases. If I click system databases, I get error " The server principle "xxx" is not able to access the database "msdb" under the current security context" .  After I give the login public role in msdb, I was able to see db, but only dbo schema displayed. 

    I tried to give  VIEW ANY DATABASE and VIEW ANY DEFINITION permission to the user , it did not help.

    This problem should be very easy to reproduce in any SQl2008  enviroment.

     

     

     

    Friday, May 14, 2010 2:23 AM
  • I guess something wrong with the Management Studio itself, I will not say permission, since I was able to query, just not able to see tables from other schema if user have access. So if i can read/query those table, i should be able to see in studio.. right ?

    (** cud be something silly, but for me its an issue!!!)

    I didn't got any proper answer for my question yet. For time being moved back to SQL 2005.

     

    When I used SQL2005 Management Studio to connect to SQL2008 database, I can see all the schemas and user databases


    When I used SQL2008 Management Studio to connect to the same SQL2008 database with same login , I can not see anything under databases. If I click system databases, I get error " The server principle "xxx" is not able to access the database "msdb" under the current security context" .  After I give the login public role in msdb, I was able to see db, but only dbo schema displayed. 

    I tried to give  VIEW ANY DATABASE and VIEW ANY DEFINITION permission to the user , it did not help.

    This problem should be very easy to reproduce in any SQl2008  enviroment.

     

    Tuesday, June 22, 2010 8:33 PM
  • I'm running into the same issue with SSMS 2008: I cannot see in the Object Explorer tables from other schema on which I have SELECT access. I can interact with them from SQL, but not from Object Explorer.

     

    I stumbled across a workaround this evening - but I'm still not sure why it works. On my machine, I can:

     

    GRANT VIEW DEFINITION TO <user>

    GRANT VIEW SERVER STATE TO <user>

     

    If I revoke either privilege, the tables disappear from the Object Explorer tree upon refresh.

     

    Q: Is this by design in SQL Server 2008 - and, if so, why?

    • Marked as answer by kumar754 Wednesday, August 04, 2010 4:25 AM
    Saturday, July 24, 2010 4:56 AM
  • I'm running into the same issue with SSMS 2008: I cannot see in the Object Explorer tables from other schema on which I have SELECT access. I can interact with them from SQL, but not from Object Explorer.

     

    I stumbled across a workaround this evening - but I'm still not sure why it works. On my machine, I can:

     

    GRANT VIEW DEFINITION TO <user>

    GRANT VIEW SERVER STATE TO <user>

     

    If I revoke either privilege, the tables disappear from the Object Explorer tree upon refresh.

     

    Q: Is this by design in SQL Server 2008 - and, if so, why?

     

    you are right, it worked for me too... I am not sure if they set it as default settings, but i didn't saw any documentation or in changes from SQL 2005. anyway.. thanks.. I am marking your comment as answer.

    Wednesday, August 04, 2010 4:25 AM
  • Having the same problem...on fresh install of  Management Studio   10.0.1600.22

    Before installing the Studio I have actually been using "Toad" version 4.6 and 5 (freeware) : NO SUCH PROBLEM with TOAD

    Oh Microsoft, you sure know how to put out crappy products....sigh

     

     

    Sunday, September 19, 2010 3:13 AM
  • Click F7. That should display what you need. For some reason, clicking the databases folder does not display the list. F7 does.

    Danny

    www.decsi.net

    Monday, April 16, 2012 10:33 PM