RE: Hiding MS SQL databases in Enterprise Manager

From: JimRuddy (
Date: 11/21/03

  • Next message: Henry David Christopherson: "iis - authenticate thru domain username/password"
    To: <>
    Date: Thu, 20 Nov 2003 20:44:00 -0500

    I think Floyd did a good job of explining the EM stuff. I wanted to chime in
    here and earlier you asked a question about whether to let developers access
    via EM and any security risks.

    If we take a step back here, I think it was your first post that said that
    you thought it was a security issue to allow the users to see the other
    databases in EM. This being said, I firmly do not allow developers' access
    to production servers but I can understand every environment is different.
    If you do not want users that are connecting to the database server to see
    the other DB's then you don't want to use EM period, I would suggest looking
    at some of the web based products out there, you can check out for some suggestions.

    What ever you decide, even if it is that users seeing the databases in EM,
    just make sure you are 100% with the assignment of permissions on the server
    and you should be pretty safe.


    -----Original Message-----
    From: Floyd Russell []
    Sent: Wednesday, November 19, 2003 12:50
    Subject: RE: Hiding MS SQL databases in Enterprise Manager

    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

    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.



  • Next message: Henry David Christopherson: "iis - authenticate thru domain username/password"