locked
SQL Management Studio Object Explorer Display RRS feed

  • Question

  • Under the Object Explorer of our SQL server, there are 30-40 objects, or data containers.  I created an account for a user to access on object, DatabaseOne, so she could login via her SQL Server Management Studion on her desktop.  In her login Property, under "User Mapping" she was given "db_Ower" right under default_schema.  When she logs on, the object explorer doesn't display DatabaseOne in the Object Explorer.  But on the right side pane, she can click on "new query".  Once typing "Use [DatabaseOne]", right clicking on "Design query in editor" she is able to see all the tables in Databaseone. She can query (select, update, add, delete) and tables.  The only thing missing is the list of these tables under Object Explorer on the left hand side.  It won't display.  If I change her permission to "View any database" under Securables, she will see the list of tables inside DatabaseONe, plus all the 30-40 databases on our server, though she can't access them but only DatabaseOne.  Still I don't want her to see the other databases but only DatabaseOne. So I disables her "view any database" permission. then she loses the list of tables in DatabaseOne in the Object Explorer.  Is there way to display tables in DatabaseOne in the Object Explorer and to hide all other databases from her?   Any help will be greatly appreciated.

    Tuesday, November 3, 2015 5:30 PM

Answers

  • You probably made her a member of the db_owner role in the database. That is not the same thing as actually being the owner of the database. Make her (login) the owner of the database as see if that sorts it. Right-click the database, Properties, Files (yeas, weird page for this) and here you can specify the owner.

    Tibor Karaszi, SQL Server MVP | web | blog

    Thursday, November 5, 2015 6:18 PM

All replies

  • Hi

    If you have given dbowner role for particular database then it can perform all maintenence and configuration activivties for that particular database.Its higher level privillege so for sure user should able to view the database.You can also check in the particular database security whether that user is added for that DB and have dbowner role as well as you can use the script like below to grant the permission.I have tested this scenario in my SQL server where i have granted dbowner  role for that particular database and I was able to view the object when I am login with that user account.

    Below script can help you

    USE [test]
    GO
    ALTER ROLE [db_owner] ADD MEMBER [test]
    GO

    Thanks

    Tuesday, November 3, 2015 7:18 PM
  • No, that is not possible.

    Unless you can make this person the owner (the real owner, not just db_owner role) of the database. If you remove view any database, you don't see any databases - except the ones you own.

    Another option might be to use containment for the database (set containment to PARTIAL), and have this person to login directly into the database - so to speak. Requires SQL 2012.


    Tibor Karaszi, SQL Server MVP | web | blog

    Tuesday, November 3, 2015 7:20 PM
  • Thank y'all for replying. I tested the login and permission again.  If the user if the owner of DatabaseOne, she is able to all datasets listed under DatabaseOne under Object Explorer, and can do all sorts of queries, select, update, add, delete on the right hand panel.  At the same time, she sees all other databases on my SQL server listed, but she can't access them.  I don't want her to even see other databases listed.  So I took her owner permission away, and gave her select, update, add and delete right.  Now when she logs on, she can't see any databases under Object Explorer.  But on the right hand panel, she can do select , update, add and delete operations for DatabaseOne.  This seems to be the best solution so far.
    Tuesday, November 3, 2015 8:48 PM
  • A login who was revoked view any database should *not* be able to see all databases just because she happens to own *one* of those databases. She should only be able to see the database(s) that she owns. There must be something else going on here. 

    Tibor Karaszi, SQL Server MVP | web | blog

    • Proposed as answer by Ice Fan Thursday, November 5, 2015 1:27 AM
    Tuesday, November 3, 2015 9:55 PM
  • I agree.  I will do more testing and see what happens.  I am using SQL Management studio 2014.  Our server is 2014. 


    Tuesday, November 3, 2015 10:12 PM
  • 
    
    
    I did more tests.  Here is the scenario : I am a SQL admin. I give a user the owner right to a database "DatabaseOne." I hope she can see evyerthing in DatabaseOne under the Object Explorer. But she can't. Only things she sees under Object Explorer are the Master and tempdb. She is able to see everhing in DatabaseOne in the right hand query windwo, when she enters use [DatabaseOne], and right clicks to select "Design Query in Editor." She is able to all all kinds of query operations in the query window. But hse really likes to see the tables adn stored procedures under Object Explorer. I just logged on as her. The interesting thing I found out was when I went to her permission under Security, in Mapped, it showed only Master nad tempdb, but she should be able to see all the datasets in DatabaseOne.  Maybe my own permission prevented it from happening?  any opinion will be greatly appreciated. 
    Thursday, November 5, 2015 5:31 PM
  • You probably made her a member of the db_owner role in the database. That is not the same thing as actually being the owner of the database. Make her (login) the owner of the database as see if that sorts it. Right-click the database, Properties, Files (yeas, weird page for this) and here you can specify the owner.

    Tibor Karaszi, SQL Server MVP | web | blog

    Thursday, November 5, 2015 6:18 PM