A while ago I blogged about instant file initialization. A colleague of mine pointed out that there is a small but non-zero risk involved with using this setting.

So I decided to put a demo together to test out the security risks involved.

I have a default instance and a named instance of SQL Server 2008 R2 running on my Windows 7 laptop. Both services are running under the local system account and therefore have the perform volume maintenance policy permissions and will use instant file initialization when creating new databases.

On the default instance create a new database:

CREATE DATABASE [SecurityTest] ON  PRIMARY 
( NAME = N'SecurityTest', FILENAME = N'C:\MSSQL\SQLData\SecurityTest.mdf' , SIZE = 2048MB)
 LOG ON 
( NAME = N'SecurityTest_log', FILENAME = N'C:\MSSQL\SQLLogs\SecurityTest_log.ldf' , SIZE = 1024MB)
GO

And a table in the database:

USE SecurityTest;
GO
CREATE TABLE PrivateData
(
data VARCHAR(20)
);

And populate the table with some “sensitive data” (this takes a minute or so on my machine to run):

DECLARE @i INT = 1;
SET NOCOUNT ON;
WHILE @i < 100000
BEGIN
INSERT INTO PrivateData VALUES ('TOP SECRET');
SET @i += 1;
END

And then delete the database:

USE [master]
GO
ALTER DATABASE [SecurityTest] SET  SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
USE [master]
GO
DROP DATABASE [SecurityTest]
GO

Now change to the named instance and create a new database in the same location as the first database:

CREATE DATABASE [SecurityTest] ON  PRIMARY 
( NAME = N'SecurityTest2', FILENAME = N'C:\MSSQL\SQLData\SecurityTest2.mdf' , SIZE = 2048MB)
 LOG ON 
( NAME = N'SecurityTest2_log', FILENAME = N'C:\MSSQL\SQLLogs\SecurityTest2_log.ldf' , SIZE = 1024MB)
GO

And have a scan through some of the pages in our new database:

DBCC TRACEON(3604) 
GO
DBCC PAGE('SecurityTest',1,300,2);
DBCC PAGE('SecurityTest',1,400,2);

You might have to experiment with different page numbers, but on my instance I get these results:

And:

Conclusion: This demonstrates the security risks involved with instant file initialization. These risks need to be balanced against the performance gains in a particular environment.