On Wikipedia there’s a short article about Windows Internal database. There are several important facts they mention, and some they don’t:
- You can connect to it with any version of SQL Server Management Studio, including SQL Server Management Studio Express
- It only allows connections for the local machine – it does not listen on TCP/IP or externally accessible named pipes.
- The pipe name you type into the server name is \\.\pipe\mssql$microsoft##ssee\sql\query
- 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. - 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
If only it were really that easy. Further investigations showed that my early enthusiasm was misplaced and that you can basically forget about using the internal database for your own stuff. Sorry if I mislead anyone.