Hide databases in EM/QA

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

slousch@gmail.com - 25 Oct 2006 08:53 GMT

I have a MSSQL 2000 SP4 with ~200 databases of our clients.
Each client can access only his own database, but in Enterprise Manager
he able to view all databases on server.
How can I hide all other databases from client?

Reply to this Message

Arnie Rowland - 25 Oct 2006 20:18 GMT

Unfortunately, with SQL Server 2000, using Enterprise Mangler and Query
Analyzer, you will find that almost impossible.

Signature

Arnie Rowland, Ph.D.
Westwood Consulting, Inc

Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous

You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf

>I have a MSSQL 2000 SP4 with ~200 databases of our clients.
> Each client can access only his own database, but in Enterprise Manager
> he able to view all databases on server.
> How can I hide all other databases from client?

Reply to this Message

Sue Hoegemeier - 26 Oct 2006 01:48 GMT

This is only true for Enterprise Manager. The users will be
able to see all the user databases although they can only
access those that they have been granted access to
explicitly or implicitly (groups, guest account
considerations).
In Query Analyzer, the databases will be restricted or
filtered to those which the user has access to.

-Sue

>Unfortunately, with SQL Server 2000, using Enterprise Mangler and Query
>Analyzer, you will find that almost impossible.

Reply to this Message

Arnie Rowland - 26 Oct 2006 06:48 GMT

Sue,

Maybe I'm doing something wrong, but this seems to work and provide a list
of all databases on the server EVEN if the login does not have permissions
for the database.

SELECT name
FROM master..sysdatabases

Signature

> This is only true for Enterprise Manager. The users will be
> able to see all the user databases although they can only
[quoted text clipped - 8 lines]
>>Unfortunately, with SQL Server 2000, using Enterprise Mangler and Query
>>Analyzer, you will find that almost impossible.

Reply to this Message

Sue Hoegemeier - 26 Oct 2006 13:19 GMT

That does not affect the view. Look at the available
databases in QA from the databases drop down.

-Sue

>Sue,
>
[quoted text clipped - 4 lines]
>SELECT name
>FROM master..sysdatabases

Reply to this Message

Arnie Rowland - 26 Oct 2006 17:09 GMT

While I agree that the databases won't show up in the Object Explorer, I
read the OP's question as "How can I hide all other databases from client?",
and I replied that that was virtually impossible.

Your rejoinder seems to indicate to the OP that is was safe to use QA since
"the databases will be restricted or filtered to those which the user has
access to."

And I disagree that is it possible to 'hide all other databases" while using
QA.

Signature

> That does not affect the view. Look at the available
> databases in QA from the databases drop down.
[quoted text clipped - 9 lines]
>>SELECT name
>>FROM master..sysdatabases

Reply to this Message