SQL Server Management Studio

  • Article

One of the key improvements in Microsoft SQL Server 2005 is the new management suite, SQL Server Management Studio (SMSS), which has many enhancements over the previous SQL Server Enterprise Manager. If you are a hoster who wants to enable the highest functionality of database administration for your customers, you can use SSMS to manage their databases.

SQL Server Management Studio Express is a slightly less functional tool that is a free download for customers. It provides much of the same functionality for database administration as SSMS without the server-specific capabilities. In most cases, end customers don't have permissions to use the server-side functionality of SSMS, because they normally only have rights to administer their database. Windows-based Hosting encourages hosters to operate in the most secure way possible; however, business and customer requirements often dictate flexibility in what you offer customers.

If you decide to offer direct access, consider recommending that your customers download the SQL Server Management Studio Express tool. It is easily available and free. The full SSMS toolset is only provided with SQL Server, so getting customer access to the tool without having SQL Server 2005 is complicated.

Dedicated Servers

If you have customers with dedicated servers running SQL Server, SSMS can be provided regardless of whether SQL Server ports are disabled at your firewall. That is because dedicated server customers typically have access to use Terminal Services and directly access their dedicated server. In that case, SSMS is installed locally on the customer’s computer running SQL Server, and they use Microsoft Remote Desktop Protocol (RDP) to connect to their server and run SSMS locally on their server.

Shared Servers

Multitenant servers running SQL Server usually contain the databases of several customers. Thus, the use of SSMS for customer database management has special requirements to further isolate the display of the database names from SSMS users. While there is no danger of unauthorized access to databases belonging to other customers, the default behavior of SSMS is to display all databases created on the server. This display issue is addressed later in this section.

SQL Server Ports: Open or Closed?

The Microsoft Solution for Windows-based Hosting recommends against leaving SQL Server management ports (UDP 1434 and TCP 1433) exposed to the Internet. Therefore, we recommend Terminal Service access to a host where SSMS is installed. This is usually not an issue for dedicated servers, as the customer administrators may have access.

Terminal Services can be used if a separate SQL Server administrative machine with SSMS installed is created. Multiple customers can use Terminal Services to access this server. This option is more costly as it requires additional hardware and Terminal Services client licenses.

Although not recommended, some service providers will decide not to close the ports. If your customers or service offerings require open ports, consider one of the following options:

  • Port Authorization — Require specific authorization to the port prior to allowing SSMS to connect. This may mean a separate logon is required prior to opening SSMS for the customer.

  • Fixed IP Port Authorization — Lock the SQL Server ports down to a specific list of Customer IP addresses. Many customer may not have fixed IP addresses, so this really is applicable to those customers that have fixed IP addresses.

  • Port Reassignment — Change the SQL Server ports to some other unused port for a specific customer. This is an intermediate step, and requires more configuration on your part for your firewall, as well as customer configuration of their SSMS tool.

SSMS Security and Display Concerns

If you decide to operate shared servers that are accessible by SSMS, consider modifying the views available so that you will hide the customer's databases from each other. By default, SSMS will display all databases on the server. For shared servers, this is a security issue. To resolve this issue, there is a view that can be revoked or denied.

  • View Any Database — Is the view that needs to be either revoked globally or denied per logon to enable SSMS to hide the databases that an end customer doesn’t have rights to access. One example of this view being used is discussed in the following section.

  • Revoke "View Any Database" from Public — Prepares the server to filter customers so they can only access their database. When databases are created on the server for the customers, those customers will not be able to see other databases.

    Dd346533.note(en-us,TechNet.10).gif Note

    Because you revoked the view from public, the end customer must be made the owner of the database to see the database within SSMS. A simple way to do this is to use the stored procedure: sp_changedbowner. With these two changes, SSMS will only display the database where the end customer has access permissions.

The example above shows the end customer as the database owner. Ensure that your provision strategy supports this level of permission for the end customer. If you desire a different level of end customer permissions, modify this for your specific requirements.

For another example of modifying the view for database permission, see

Limiting views on shared SQL server installations

For shared SQL Server installations, SSMS will allow all customers to see the names of other databases. You can change that default behavior with a SQL Server 2005 configuration change.

To do so, on your SQL 2005 servers, revoke "View Any Database" from public. Then, customers of a shared server, will only see their database, provided you've made the customer the owner of the database. An easy way to do this is with the stored procedure sp_changedbowner or with Alter Database.