RE: Hiding MS SQL databases in Enterprise Manager

• Previous message: Mike Theriault: "RE: Hiding MS SQL databases in Enterprise Manager"
• In reply to: Mike Theriault: "RE: Hiding MS SQL databases in Enterprise Manager"
• Next in thread: JimRuddy: "RE: Hiding MS SQL databases in Enterprise Manager"
• Reply: JimRuddy: "RE: Hiding MS SQL databases in Enterprise Manager"
• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]

To: <focus-ms@securityfocus.com>
Date: Wed, 19 Nov 2003 11:49:50 -0600

I think somewhere there is some mis-information begin spread.

Using Enterprise Manager does not inheritly mean that the user
is connecting with the sa account. Enterprise Manager can be
used by any SQL login with enough privileges to connect to the
server.

As for the whole hiding database problem, this is something that
I have fought for close to four years with no good solution. The
problem is that the stored procedure, sp_MSdbuseraccess, is first
called to get a list of databases that the user has access to.
But then immediately following this command is executed:
[Forgive the nastiness]

select name, DATABASEPROPERTY(name, N'IsDetached'), (case when
DATABASEPROPERTY(name, N'IsShutdown') is null then -1 else
DATABASEPROPERTY(name, N'IsShutdown') end), DATABASEPROPERTY(name,
N'IsSuspect'), DATABASEPROPERTY(name, N'IsOffline'),
DATABASEPROPERTY(name, N'IsInLoad'), (case when DATABASEPROPERTY(name,
N'IsInRecovery') is null then -1 else DATABASEPROPERTY(name,
N'IsInRecovery') end), (case when DATABASEPROPERTY(name,
N'IsNotRecovered') is null then -1 else DATABASEPROPERTY(name,
N'IsNotRecovered') end), DATABASEPROPERTY(name, N'IsEmergencyMode'),
DATABASEPROPERTY(name, N'IsInStandBy'), has_dbaccess(name), status,
category, status2 from master.dbo.sysdatabases

This command takes no permissions into account, it just generates
the full list of databases in sysdatabases and their state. This list
is then used by Enterprise Manager to create the list of databases.

The stored procedure mentioned earlier, sp_MSdbuseraccess, is a very
ugly chunk of code. If I recall it does a double iteration of the
databases for no reason - slowing the overall initial connection. I
did some *tweaking* of it a long time ago to be overall more efficient.

It would be conceivable to limit the user access if Enterprise Manager
didn't use that nasty chunk of ad hoc SQL to get the list of databases. But,
as long as that code is executed all databases are going to be listed
for a particular SQL Instance.

Also, keep in mind that running multiple SQL instances on SQL Standard
Edition and making them accessible to web applications may not be permitted
by your license from Microsoft.. of course YMMV.

Floyd R.

---------------------------------------------------------------------------
---------------------------------------------------------------------------

• Previous message: Mike Theriault: "RE: Hiding MS SQL databases in Enterprise Manager"
• In reply to: Mike Theriault: "RE: Hiding MS SQL databases in Enterprise Manager"
• Next in thread: JimRuddy: "RE: Hiding MS SQL databases in Enterprise Manager"
• Reply: JimRuddy: "RE: Hiding MS SQL databases in Enterprise Manager"
• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]