This is one of the common questions I have seen on forums, most common request from administrators who have been given task to create multi-tenant SQL Server. This essentially means various department of company or various client of the hosting company would access same SQL Server Instance. Business owners demand that they want clients to see only those databases which are relevant for them. I have worked with a company where the database name was name of the client. Inability to hide name of databases would essentially mean exposing information about their other clients.

In simple words, if Contoso is a database hosting company who has “Northwind traders” and “TailSpin Toys” as their client. If TailSpin logs in to the SQL Server, they should not see “Northwind” database and vice versa.

SQL Server Enterprise Manager calls the sp_MSdbuseraccess stored procedure to determine the accessibility of each database. The sp_MSdbuseraccess stored procedure then runs against every database in SQL Server to determine the user’s permission level in that database. So this can be achieved by altering the stored procedure as documented in KB http://support.microsoft.com/?id=889696 (You may experience a slow response from SQL Server Enterprise Manager when many databases exist in an instance of SQL Server)

In SQL 2005 onwards we fire queries to get the information in SQL Server Management Studio (a.k.a. SSMS) . Now, SQL Server 2005 onwards, we also have limited metadata visibility which I am going to use as “trick” to achieve our goal. As per http://msdn.microsoft.com/en-us/library/ms178534.aspx
{

If the caller of sys.databases is not the owner of the database and the database is not master or tempdb, the minimum permissions required to see the corresponding row are ALTER ANY DATABASE or VIEW ANY DATABASE server-level permission, or CREATE DATABASE permission in the master database. The database to which the caller is connected can always be viewed in sys.databases.
}

So, here is the quick script which can be used to demo this. If you love SSMS then I have also given instruction.

• Create Two databases.

Create Database [NorthWindDB]
go
Create Database [TailSpinDB]
go

• Create Logins (Not mapped to any Database)

CREATE LOGIN [NorthWindLogin] WITH
PASSWORD=‘E@syP@ssw0rd’
go
CREATE LOGIN [TailSpinLogin] WITH
PASSWORD=‘E@syP@ssw0rd’
go

• Right Click on the Root section of the SSMS Tree (SQL Server Name)>Properties>Permissions>Click on the account, and select Deny to view databases. OR use below T-SQL

REVOKE VIEW ANY DATABASE TO [NorthWindLogin]
go
REVOKE VIEW ANY DATABASE TO [TailSpinLogin]
go

• Right Click on the newly created databases > Properties > Files, and change the Owner to the newly created accounts. OR use below T-SQL

use [NorthWindDB]
go
sp_changedbowner 'NorthWindLogin'
go
use [TailSpinDB]
go
sp_changedbowner 'TailSpinLogin'
go

Here is the final out come…

NorthWindLogin can see only NorthWindDB database and TailSpinLogin can see only his database.

Erland Sommarskog, SQL MVP, has filed request “Need VIEW DEFINITION permissions per database”. If you feel this is useful then please vote here!

Hope this would help someone in the world!

Cheers,
Balmukund Lakhani
Twitter @blakhani

While working on a case few days ago, I faced this question by customer and i found it interesting to blog about. (Today I found this in forum so finalizing and posting it). Here is the question.

I have created two node windows cluster. All disk are available in “storage” but I don’t see any option to do manual failover unless they are part of some group. How can I test if failover is working fine for disks without shutting down the node or adding them to a group.

Here is the screenshot to explain the problem better. As you can see that U and W are not part of any group defined under “Services and Applications”. So by default they are the part of “Available Storage” group.

And indeed there is no option in UI to do failover of those disks.

I spend sometime with windows team and I was able to find the answer. so here it goes.
This can only be done by command line. We need to use cluster.exe command to achieve this.

Cluster Group “Available Storage” /Move:NodeName

In above command NodeName is optional. If you have two nodes, it would move storage automatically to other node.

Hope this would help someone in the world!

Cheers,
Balmukund Lakhani
Twitter @blakhani

Sometime I have been asked that how to find out who dropped/altered/created table/stored procedure. Well, SQL 2005 has out-of-the-box solution to this problem. This works in SQL 2008 as well.

Schema Change History is the report which would give details. This report takes data from default trace which is enabled by default in SQL Server 2005 default installation. Below command will help you in finding current status.

USE master;
EXEC sp_configure ‘show advanced option’, ’1′;
RECONFIGURE WITH OVERRIDE;
EXEC sp_configure ‘default trace’;
GO

If its enabled you would see run_value as 1.

More about default trace can be read here

If you want to see who dropped a database, you can right click on Server Icon and go to Reports > Standard Reports > Schema Changed History. This Reports output would changed based on where you right clicked it (on server icon or database name)

Since default trace will be maximum 100 MB in size you may not get the data which is pretty old. Every restart also creates new file. So, if your SQL Server has restarted five times after someone dropped the object, you will not be able to see it here.

SQL Server 2005 has many other “Standard Reports” which can be found in SQL Server Management Studio.

Enjoy!!!

<Tags> Who dropped my database, when database was dropped, someone deleted database objects, tables, procedures, list of dropped objects in SQL Server 2005, Schema Change history Report, see who dropped databases, how to find who dropped the database in SQL server 2005 objects dropped</Tags>

Cheers!
Balmukund