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:
  6. — 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
This entry was posted in Uncategorized. Bookmark the permalink.

21 Responses to Windows Internal Database

  1. Alasdair says:

    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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s