Databases not showing in Enterprise Manager

Question

Post reply

Databases not showing in Enterprise Manager

Viewing 15 posts - 16 through 30 (of 31 total)

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

santveer SSC-Addicted Points: 447 More actions · Answer 1

santveer

SSC-Addicted

Points: 447

May 5, 2005 at 3:50 pm

#557236

Chris,

Just check the database properties for all the databases using sp_helpdb in QA, Are owner and stutus same for all databases.

Santveer

1234

lholman SSCrazy Points: 2810 More actions · Answer 2

Chris,

I got exactly the same error when I removed the guest user from the master database (as I stated in my earlier posts) and tried to create it again with EM, there was obviously an issue with the relationships held within asscociated system tables as there are many derived columns within the system tables, i.e. like when you have to use sp_change_users_login to re-associate the SID's when you restore a database from a separate server.

What i'm trying to say is it looks like you have an underlying issue with some system tables and associated column entries for that user within the master db. When did this problem start? Have you got a backup of the master database you could restore and or compare against?

Lloyd

shashi-222553 SSC Enthusiast Points: 196 More actions · Answer 3

Chris,

I believe - there are some conflicts between the OS configurations (may be the service packs or something) and the SQL Server. It could be some of the System DLLs - which are not fine.

I encountered the same problem for one of my installation here in India - where I had to connect my laptop with the server and use my Enterprise Manager to talk to the SQL Server on the Server machine. Enterprise Manager was behaving the same way.

SQL QA - worked fine in some cases - sometimes - it threw some exceptions!!

I am not sure, if this could help! - but just a hint,

Rgds,

Shashi

Yelena Varshal SSC-Dedicated Points: 34286 More actions · Answer 4

My feeling are too towards Guest account. Lloyd has a point here. As I said, when I removed Select on Sysdatabases from Guest I could see all the same "No Items" in EM and normal list of databases in QA for non-admin users. Admin user was able to see databases in EM

So my suggestion that does not fix the problem but fixes the symptoms: Chris should give Select permission on system tables (at least Sysdatabases) to his non-admin user so this user will not be Guest in Master.

Yelena

Regards,Yelena Varsha

Christopher Wright Ten Centuries Points: 1219 More actions · Answer 5

Wow, lots of great ideas. I'm just now getting a chance to work on these today, but I did dig up a few interesting tidbits just now.

As is usually the case, network admins and those using the systems don't always effectively communicate. A nightly backup is hitting my data directories, and we don't have the SQL Server agent to support that. I'm lucky anything at all still works.

I'm going to try all of your suggestions and get back to you.

-Chris

Christopher Wright Ten Centuries Points: 1219 More actions · Answer 6

Okay,I have done a couple of things to see if they would help in accordance with the suggestions here. First, I made the guest account dbo in the master. Just temporarily, but I wanted to see if it helped. That did not work out.

I also placed my user into the master as a dbo, and that didn't work either.

Still working on it...

Christopher Wright Ten Centuries Points: 1219 More actions · Answer 7

Christopher Wright

Ten Centuries

Points: 1219

May 9, 2005 at 8:55 am

#557628

Interesting twist here...if I hit refresh and wait for like 2-3 minutes, the databases list out fine.

Still not ideal, but better. Any ideas?

-Chris

Yelena Varshal SSC-Dedicated Points: 34286 More actions · Answer 8

Chris,

Please, remove Guest from db_owner in Master. I am not confortable with that. I would just give Select on all objects in Master to this user account.Then completely disconnect or restart Enterprise Manager.

Does it still take 2 or 3 min for databases to show? At this point I would run Profiler for just the action of expanding Databases container in EM by this user for T-SQL Statement Started and Statement Completed events. We will be able to see what statements are executed and what statements are started but not completed or completed later. But most importantly add:

- Audit Object permission Event

-Audit Object Derived Permision Event

-Audit Statement Permision Event

Do add TextData column for your text description

Yelena

Regards,Yelena Varsha

Christopher Wright Ten Centuries Points: 1219 More actions · Answer 9

Hi Yelena,

I removed dbo immediately. It's a dev box, so it's not crucial, but I'm with you on the guest access to master.

Now, here's the kicker. I think I fixed the problem. The Windows event log has a data portion that can be switched from bytes to words. In this area, the Northwind database was being referenced on unsuccessful login attempts, and the master was being referenced on successful attempts. I deleted the Northwind database, and bang. Everything is right with the world again.

I'm going to give it some more time to play out, but I think that's it. I'll post back later to let you all know.

Thanks so much for all of your help! Hopefully, this will be the end of this particular issue.

-Chris

gladiator9 SSChasing Mays Points: 660 More actions · Answer 10

gladiator9

SSChasing Mays

Points: 660

May 17, 2005 at 6:02 am

#559222

So was this the resolution? I have exactly the same issue. Overnight I can no longer view any databases or anything under the management folder using anyuser id inc sa. Although query analyzer works fine ?

Christopher Wright Ten Centuries Points: 1219 More actions · Answer 11

For me, this was actually the resolution. It's been pretty amazing. I have no clue how the problem came to be, but this seemed to solve it entirely.

Look at the Windows Event Log. Any error with a severity that is high enough is automatically logged there. Most of the data portion of the errors are simply numbers, but you will be able to read the server and database name. On login, you should see that it's hitting the master database.

Hope this helps you. There was no rhyme or reason for the sudden failure, though.

-Chris

gladiator9 SSChasing Mays Points: 660 More actions · Answer 12

gladiator9

SSChasing Mays

Points: 660

May 17, 2005 at 6:22 am

#559225

Thanks, funny thing is the event viewer for the server is completely empty.Likewise I have no idea why this is happening. I'll keep this thread open until fixed.