Databases not visible in Enterprise Manager

[cmwright]

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 Databases, 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.

 

[mrdenny]

I take it this happens for anyone who is not a sysadmin?

Make sure that the guest account is in the sysusers tables in each database. If the guest account is removed from the databases the Enterprise manager won't show the databases correctly.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)

 

[cmwright]

Hi Denny,

That's not quite true. In fact, according to rules passed down from the Department of Defense, guest must be removed from all of my databases except master and tempdb. That said, I have not removed any guest accounts on this server yet.

Also, I checked the event log, and got a lot of these errors:

Error 17052 / SQLDumpExceptionHandler: Process generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.

Still investigating. If you have any other ideas, please let me know.

-Chris

 

[vongrunt]

I think mrdenny is at least indirectly correct. Check sproc sp_MSdbuseraccess in master DB for more details, branch for @mode='db'.

------
heisenbug: A bug that disappears or alters its behavior when one attempts to probe or isolate it
schroedinbug: A bug that doesn't appear until someone reads source code and realizes it never should have worked, at which point the program promptly stops working for everybody until fixed.
[ba

 

[mrdenny]

If you remove the guest account from all the databases any user who is not a sysadmin will not have much luck using Enterprise Manager.

17052 looks like it can mean a few things. Take a look at the ERRORLOG. You will probably see several lines of messages around the error lines. Please post them and we can probably give you a better idea as to what's doing on.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)

 

[cmwright]

Hi Mr. Denny,

Thanks for responding. I haven't removed the guest accounts at all on this server. That should be okay.

The log says "Using dbghelp.dll version 4.0.5" and "Stack signature for the dump is 0x3D700073." and "Error: 0 Severity: 19 State: 0."

And then there is the stack dump file that says that you need to send it to Microsoft.

Strange.

-Chris

 

[cmwright]

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 when I have given more time to see if the situation has cleared up.

Thanks so much for helping me.

-Chris

 

[mrdenny]

I would assume that there is something more going on here. Unsuccessfull login attempts to a database shouldn't cause the SQL Server to stack dump.

A stack dump almost always means that there is something else going wrong with the server.

According to BOL a severity 19 SQL Error is:

Severity Level 19: SQL Server Error in Resource

These messages indicate that some nonconfigurable internal limit has been exceeded and the current batch process is terminated. Severity level 19 errors occur rarely; however, they must be corrected by the system administrator or your primary support provider. The administrator should be informed every time a severity level 19 message occurs.

I would still recommend having microsoft take a look at the dump files before the problem shows up again.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)

 

[cmwright]

Thank-you. I will take your advice. Much appreciated!

-Chris