Windows Internal Database

On Wikipedia there’s a short article about Windows Internal database. There are several important facts they mention, and some they don’t:

1. You can connect to it with any version of SQL Server Management Studio, including SQL Server Management Studio Express
2. It only allows connections for the local machine – it does not listen on TCP/IP or externally accessible named pipes.
3. The pipe name you type into the server name is \\.\pipe\mssql$microsoft##ssee\sql\query

    

4. Although you can create databases and drop objects, the super-secure mechanism to stop you using it for your own purposes is that you can’t create objects (tables, stored procs, etc). What you get is:

   Msg 33003, Level 16, State 1, Line 1
   DDL statement is not allowed.

5. The workaround for point 4 above is to put the database in single user mode while you’re doing the DDL:

— before

alter database banana set single_user;

go

 

— create whatever

if object_id( ‘bob’ ) is not null

      drop table bob;

 

create table bob (x int );

go

 

— after

alter database banana set multi_user;

go

Advertisements