RE: Hiding MS SQL databases in Enterprise Manager

From: JimRuddy (jruddyse_at_hotmail.com)
Date: 11/21/03

  • Next message: Henry David Christopherson: "iis - authenticate thru domain username/password"
    To: <focus-ms@securityfocus.com>
    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
    www.sqlmag.com 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.

    JimR

    -----Original Message-----
    From: Floyd Russell [mailto:floyd@neospire.net]
    Sent: Wednesday, November 19, 2003 12:50
    To: focus-ms@securityfocus.com
    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
    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.

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

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


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

    Relevant Pages

    • Re: Side-by-side upgrade - moving master, msdb and model
      ... MVP - Windows Server - Clustering ... "In some cases, you may want to copy the system databases, including ... from the source SQL Server 2000 instance to the SQL ...
      (microsoft.public.sqlserver.clustering)
    • Re: BizTalk Server 2004 ConfigFramework.exe error
      ... one BizTalk is installed on? ... machines (both for SQL and DTC) is not blocked by firewalls and new MSDTC ... Multi-computer configuration using Windows Server 2003 or Windows XP ... >>all associated databases. ...
      (microsoft.public.biztalk.general)
    • RE: Clustering a standalone SQL server + storage array
      ... working as expected with out error your ready to install sql server. ... log files to the data and log shared drives you setup as part of the cluster. ... Our apps team look after a trading system that uses an HP DL360 server ... running SQL 2000 SP3a with a few small databases on it. ...
      (microsoft.public.sqlserver.clustering)
    • Re: Side-by-side upgrade - moving master, msdb and model
      ... MVP - Windows Server - Clustering ... "In some cases, you may want to copy the system databases, including the ... from the source SQL Server 2000 instance to the SQL ...
      (microsoft.public.sqlserver.clustering)
    • Re: SQL 2005 servers with multiple instances
      ... Microsoft SQL Server MVP ... given the same number of user databases. ... we've decided to use SQL 2005. ... SQL 2005 clustering? ...
      (microsoft.public.sqlserver.setup)