Post reply

Databases not showing in Enterprise Manager

  • May 5, 2005 at 5:46 am

    #88862

    Hi all,

    I am having a problem getting databases to show up in Enterprise Manager, SQL Server 2000 Standard Edition (SP3). I have created a SQL Server authenticated login and made that login dbo for 7 databases. When that user tries to connect, the registration is successful, but no databases are listed in Enterprise Manager.  When you expand the Databases node, it says (no items). This is interesting, because Query Analyzer shows some of the databases in the Object Browser, but all of them in the databases dropdown at the top.

    I have tried deleting the registration and starting over, and I have also tried rebooting the server. I'm finding nothing on the Internet about this, either. Any clues?

    -Chris

    PS I have had this happen with Windows authentication as well.

  • May 5, 2005 at 5:55 am

    #557017

    Hi Chris,

    I think - you just need to register the native SQL Server Service in the Enterprise Manager.

    • Find out the host name. --You can find out the hostname ('By going to command prompt and typing the hostname').
    • Right click on the sql Server node - Select the new SQL Server registration - follow the process - till it asks you the sql server name (machine name) or the IP Address.
    • You can either type in the hostname in this window - or alternatively you can also specify the IP address of the machine on which SQL server is installed.
    • Click Next - Follow the rest of the procedures.

    Wishes,

    Shashi

  • May 5, 2005 at 6:08 am

    #557022

    Hi Shashi,

    I have registered a new server multiple times in the past.  I have used both the IP and the hostname, neither of which worked.  I made the user a server admin, and everything showed up fine.  The user won't be an admin, though, so that had to be removed. 

    Any other ideas?  I am starting to suspect network policies are affecting me here, but I have another server where this is not an issue.

    -Chris

     

  • May 5, 2005 at 9:35 am

    #557113

    You'll need to make sure that the user has access to each of the databases that they need to view.  From EM, Click on Security, Logins, then right click on the login and select Properties.  Make sure the user has access by checking the Database Access tab for each database.  If needed, you can assign the user a fixed database role such as db_datareader or something similar depending on what level of priveleges you want to give them.

    Interestingly, I don't think that you can provide most users access to even see the database if it's been set to Read Only mode.  I could be wrong, but I think only sa's can view read only DB information.  Hope this helps. 

     

    My hovercraft is full of eels.

  • May 5, 2005 at 9:44 am

    #557120

    Chris,

    Could you check that the 'guest' user exists in your master database on the server and if so that the 'guest' user is part of the 'public' role within the master database.  I think i've seen this before where by the logon has had no permissions to access the master db and therefore SQL server cannot provide an enumeration of the databases to the login, thus preventing access.

    Lloyd

  • May 5, 2005 at 9:59 am

    #557127

    Just to follow up on my last comment, I know that EM stops you from accidentaly removing the guest user from master (and tempdb) but it is possible through query analyser.  However I just tried this on a test server and it seems to cause a whole host more problems than what you are experiencing. 

    Lloyd

  • May 5, 2005 at 10:08 am

    #557133

    Interesting thought.  Guest exists in the master and has the appropriate role.  Query Analyzer can hit the databases to which I have given access, so this is an Enterprise Manager oddity, though as I said before, it carries over to Query Analyzer because the object browser displays an incomplete list of allowed databases, and the dropdown database selector shows them all.

    I can run SQL against any of these databases.  They just aren't showing up. 

  • May 5, 2005 at 10:23 am

    #557146

    Chris,

    Did you check only db_owner group for that user? I had to help in several cases when ALL roles in the database were checked for a user, including, well db_denydatareader and db_denydatawriter. In this case the login cannot access databases even if he is a member of db_owner group.

    The fact that server administrators who are probably Builtin/Administrators can see databases shows that it is permission problem. Do check what permissions public role is given as well as other roles / groups this user is a member of.

    Did you connect in Query Analyzer as the same user or different login?

    Yelena

    Regards,Yelena Varsha

  • May 5, 2005 at 11:37 am

    #557183

    Hi Yelena,

    I should probably qualify that I am a SQL Server DBA.  Not that it makes a difference.  The questions are quite fair.  I am positive, though, that I am not making rookie mistakes.

    The behavior can't be traced to anything in particular.  I haven't locked down the box any more than it was initially since before things were working fine.  I am also the only one touching the box, administratively. 

    I removed the Builtin\Administrators group and added my own.  I have many rules that must be followed here.  I didn't apply the server role to a windows user.  I applied it to a SQL user.  I think you are correct.  Looks like permissions, but what permissions?  I haven't changed the configuration of the server.

    I connect to QA as the same user.  I can see some databases in the Object Browser, and all of them in the dropdown.  Very odd.

    Roles have not changed.  I am very strict when it comes to changing things of that nature.

    Keep the suggestions coming!    Thanks a lot!

    -Chris

  • May 5, 2005 at 12:32 pm

    #557197

    Chris,

    I just reproduced this problem. I just denied Select on Sysdatabases in Master for user Guest. I was connected to SQL server as a low-privileged login, I could see all databases, but after that I denied Select on SysDatabases in Master to Guest (using another connection in my own high-priv login name), and my low-privileged login did not have roles in Master , so he / she /it was a guest in Master, and now he/she/it can not select. I diconnected and re-connected my EM to the server. Well, the database list says "No Items"

    So you may want to check what permissions Guest has in Master, specifically on Sysdatabases where the list of databases comes from.

    Hope it will help...

    Yelena

    Regards,Yelena Varsha

  • May 5, 2005 at 12:47 pm

    #557201

    Hi Yelena,

    Thanks for the reply.  Unfortunately, no dice.  I have a production server that is behaving well.  I compared the permissions given to Guest on both, and they're identical.

    Additionally, I was wondering why there was a discrepancy between Enterprise Manager and Query Analyzer.

      Back to the drawing board. 

    -Chris

  • May 5, 2005 at 1:02 pm

    #557204

    Chris,

    Is it the only thing you wonder?

    I have the same thing. What I did now is to remove a check mark on Select for Public in Sysdatabases. Then connected as a low-priv. user and again, he/she/it can see only "No Items" in EM. The I clicked on Tools->Query Analyzer so I am connected as the very same user and I CAN see databases in QA object browser and Database dropdown. But when I run a statement being in Master Select * from sysdatabases" it rightfully says that Select permission denied.

    So the effective setting now for sysdatabases: No checkmarks for both Public and Guest on any permission.

    At the same time being connected as a member of Windows Administrators by another connection I can see ALL databases in EM.

    Do not forget that permissions or their abcence can come from another level.

    Yelena

    Regards,Yelena Varsha

  • May 5, 2005 at 1:18 pm

    #557210

    Hi Yelena,

    I think I need to look in a different direction.  I checked the error log, and I have tons of Error 17052 / SQLDumpExceptionHandler: Process generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION.  SQL Server is terminating this process.

    That leads me to think there's more to this than permissions.  More to come! 

    -Chris

  • May 5, 2005 at 2:03 pm

    #557218

    Chris,

    In this case check out this:

    http://support.microsoft.com/default.aspx?scid=kb;en-us;248749

    It is for SQL Server 7, but the error mesage essentially the same. It talks about TCP/IP packets. Just for the try, did you try Named Pipes? I still thing it is a permission issue since administrators can connect, but why not to try? Maybe you have 2 independent problems that happens. Even ythe best DBA may have them.

    And please, let us know what  is the cause of you problem when you find one.

    Yelena

     

    Regards,Yelena Varsha

  • May 5, 2005 at 2:49 pm

    #557229

    Hi Yelena,

    Please don't get me wrong.  I think you are dead-on 100% right.  I tried all of your suggestions.  I just haven't found the solution yet.  It's got to be a permissions issue.

    I'm going to keep looking for permission problems.  It's just frustrating to know that I haven't touched anything, and this problem has popped up.  I'm sure everyone can relate. 

    Anyhow, I really appreciate your help.  More to come tomorrow.  Thanks so much!

    -Chris

Viewing 15 posts - 1 through 15 (of 31 total)

You must be logged in to reply to this topic. Login to reply