Administering SQL Express without Management Studio

Call me a glutton for punishment, but today I endeavored to create a database and user w/o the aid of SQL Management studio.  Why would you do something so ridiculous you ask?  First, my new shop is more of an Oracle group that tolerates MS, so I don’t have Management Studio installed yet.  Second, I think you learn more about a product when you start using it command-line.  No hand holding, point and click stuff here.  Just raw ascii and endless error messages.  Anyway, I got it working so here’s the script to create a database and a login for it:

 

create database TestLink

go

create login testlink with password = 'abc123', DEFAULT_DATABASE = testlink

go

use testlink

create user testlink with default_schema = dbo

go

sp_addrolemember 'db_owner', 'testlink'

go

 

Btw, if you’re SQL Express install is fresh then it is in Windows Authentication mode and needs to be changed to Mixed mode (restart SQLEXPRESS service required):

 

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer

LoginMode = 2