Databases not showing in Enterprise Manager
-
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 -
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
-
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
-
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
-
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
-
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...
-
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
-
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
-
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
-
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 ?
-
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
-
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.
-
Awesome. There are apparently a lot of people who have had similar problems. However, I've rarely seen two people with the same problem.
-Chris
-
Yes, doesnt help as I want to remove the database hence I took it offline. Would like to delete but worried of a nasty consequence.
Viewing 15 posts - 16 through 30 (of 31 total)
You must be logged in to reply to this topic. Login to reply