SQL Server Management Studio - Limiting views on shared servers
Posted
Monday, April 17, 2006 11:35 AM
by
HosterPoster
Shared servers usually have many databases belonging to different customers, and MS SQL Server Management Studio (SSMS) will display all those databases when used by a valid login to connect to SQL Server 2005. If you seek to change that default behavior, you can do so with a SQL Server 2005 configuration change.
Here's how to make the change.
Simply revoke View any database from public, on your SQL 2005 servers, and 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 (Alter Database can be used too)
Here's simplistic sample T-SQL that illustrates the procedure to affect the change on SQL 2005 and for making the customer the owner. Remember, this setting doesn't exist in SQL 2000, this is for SQL 2005 only.
use master
Revoke View any database from public
go
Now that the server's behavior has been modified, you'll need to ensure that your new logins are mapped as the owner of the DB, so SSMS will display properly. This enables the owner of the database to see their DB, and only their DB when using SSMS to connect and manage their database. Other logins added to the dbo role within the Database don't get the same benefit. They can connect of course, because they are in the dbo role, but the object explorer in SSMS won't display the DB name, they can use query editor, to update and modify database elements.
Create Login newuser with password = '12%E56WW'
go
Create Database newuserdb
go
use newuserdb
Exec Sp_changedbowner 'newuser'
This sample uses SQL Security, if you need more information on modifying the T-SQL, to use the various arguments available when creating Logins or databases, or to use the windows security model, please refer to SQL 2005 books online. There are many great examples there.
Remember, this alters the view seen by users of SSMS against a 2005 Server. SSMS when used against a 2000 server, will show the many databases. (Even if you've applied the hot fix http://support.microsoft.com/kb/889696/en-us for SQL Server 2000 to enable the similar limited views functionality from Enterprise Manager connecting to SQL 2000.
There is an impact that accompanies this server setting change. Please ensure you understand it by validating it in your test environment, and confirming it meets your business requirements.
Regards