Databases not showing up in Enterprise Manager

[Lyle H. Gray]

Enterprise Manager for MSSQL 2000 (V8.0), connecting to MSSQL 7 database
server:

Some logins can see the listing of all databases through Enterprise
Manager, some cannot. Logins are set up as users in the same databases,
and do not have any special server roles.

If it makes any difference, none of the logins are tied in to the Windows
logins.

Any idea why some of these logins can't see the databases?

Regards,
Lyle H. Gray

[Dan Guzman]

> Any idea why some of these logins can't see the databases?

I don't have a SQL 7 instance handy but I believe EM uses the HAS_DBACCESS
function to show/hide databases any database listed by the query below
should be listed and available to the user. Conversely, databases not
returned by the query ought not to be listed

SELECT
name
FROM master.dbo.sysdatabases
WHERE has_dbaccess(name) = 1

Since you've already checked system roles, I suggest you check login/user
mapping using sp_helplogins. If incorrect, you can correct using
sp_change_users_login.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Lyle H. Gray" <gr**@no.spam.cs.umass.edu.invalid> wrote in message
news:Xn**********************************@192.168. 1.104...

Enterprise Manager for MSSQL 2000 (V8.0), connecting to MSSQL 7 database
server:

Some logins can see the listing of all databases through Enterprise
Manager, some cannot. Logins are set up as users in the same databases,
and do not have any special server roles.

If it makes any difference, none of the logins are tied in to the Windows
logins.

Any idea why some of these logins can't see the databases?

Regards,
Lyle H. Gray

[Lyle H. Gray]

"Dan Guzman" <gu******@nospam-online.sbcglobal.net> wrote in
news:XC*******************@newssvr11.news.prodigy. com:

Any idea why some of these logins can't see the databases?

I don't have a SQL 7 instance handy but I believe EM uses the
HAS_DBACCESS function to show/hide databases any database listed by
the query below should be listed and available to the user.
Conversely, databases not returned by the query ought not to be listed

SELECT
name
FROM master.dbo.sysdatabases
WHERE has_dbaccess(name) = 1

Here I might have something.

When I execute this query from my own (SA) login, I get a full list of
the databases, as I would exepct. When I run it from one of the logins
in question, I get a list of the databases that that login should have
access to as a user, but I also get the following message:

ODBC: Msg 0, Level 19, State 1
SqlDumpExceptionHandler: Process 13 generated fatal exception c0000005
EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.

The process number would obviously change each time, but I expect that
what is happening is that the error stops Enterprise Manager from
displaying the database list.

I'll see if there's any information about this in the KB.

Thanks for the help,
Lyle

[Dan Guzman]

> ODBC: Msg 0, Level 19, State 1

SqlDumpExceptionHandler: Process 13 generated fatal exception c0000005
EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.

The process number would obviously change each time, but I expect that
what is happening is that the error stops Enterprise Manager from
displaying the database list.

I'll see if there's any information about this in the KB.
If you can't find a KB article, see if the problem persists between server
restarts and run DBCC CHECKDBs to rule out data corruption. Even if EM
doesn't display an error, you should get a corresponding AV error in the SQL
Server error log.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Lyle H. Gray" <gr**@no.spam.cs.umass.edu.invalid> wrote in message
news:Xn**********************************@192.168. 1.104... "Dan Guzman" <gu******@nospam-online.sbcglobal.net> wrote in
news:XC*******************@newssvr11.news.prodigy. com:

Any idea why some of these logins can't see the databases?

I don't have a SQL 7 instance handy but I believe EM uses the
HAS_DBACCESS function to show/hide databases any database listed by
the query below should be listed and available to the user.
Conversely, databases not returned by the query ought not to be listed

SELECT
name
FROM master.dbo.sysdatabases
WHERE has_dbaccess(name) = 1

Here I might have something.

When I execute this query from my own (SA) login, I get a full list of
the databases, as I would exepct. When I run it from one of the logins
in question, I get a list of the databases that that login should have
access to as a user, but I also get the following message:

ODBC: Msg 0, Level 19, State 1
SqlDumpExceptionHandler: Process 13 generated fatal exception c0000005
EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.

The process number would obviously change each time, but I expect that
what is happening is that the error stops Enterprise Manager from
displaying the database list.

I'll see if there's any information about this in the KB.

Thanks for the help,
Lyle