RE: Hiding MS SQL databases in Enterprise Manager

From: Floyd Russell (
Date: 11/19/03

  • Next message: Mason, Samuel: "IIS traffic"
    To: <>
    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

    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: Mason, Samuel: "IIS traffic"

    Relevant Pages

    • Re: How to check if SQL 2000 is installed? (SBS 2003 SP1 Problem!)
      ... > Enterprise manager" and view the databases running on your server (just ... > click the + until you see databases), then you definitely have a SQL ... But I have sometimes installed two Tools called "MSDE Manager" and "MS SQL ...
    • Re: How can I grant user run cmdexec
      ... This can happen when the startup account for SQL Server ... could reset the service accounts through Enterprise Manager ... Change the SQL Server or SQL Server Agent Service ...
    • RE: Error 22022 when running SQLservices under different accounts
      ... If you are using the SQL Enterprise Manager to change the service ... startup accounts to use a non-admin account, ... How to change the SQL Server or SQL Server Agent Service account without ... Microsoft SQL Support ...
    • Re: denying permissions on the views underlying tables
      ... (running NT4, SP6a). ... see the thread "one SQL DB getting info form another - user ... >>passing between databases on the same server. ... >>account is defined, then that account must have rights. ...
    • Re: Enterprise Manager for shared server
      ... Autoclose turned off on your databases as well. ... Andrew J. Kelly SQL MVP ... > that are maintained on this same server. ... > 2-3 minutes for Enterprise Manager to propagate the ...