Notes on setting up SQLServer Express instance

=> Command line to open after install
C:\Users\Tim>sqlcmd -S .\SQLEXPRESS

=> SQL command to create a database
create database data_clean
go

=> SQL command to create a login
create login timdb with password = '*****' 
go 

=> SQL command to change database

use data_clean
go

Changed database context to 'data_clean'. 

=> SQL command to add user to database

create user timdb for login timdb 
go 

=> SQL command to give user db_owner permissions

EXEC sp_addrolemember N'db_owner', N'timdb'; 
go

==> set the default database for the user
exec sp_defaultdb @loginame='timdb', @defdb='data_clean';

=> SQL command to enable sa

alter login sa enable; 
go 
Now a geeky bit - to allow SQL authentication rather than Windows

update registry key 
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQLServer\LoginMode 

Set Dword from 1 to 2 restart sqlserver

=> set default database for sa
exec sp_defaultdb @loginame='sa', @defdb='master';

alter login sa with password = '*****';
go
Advertisements

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 )

Google+ photo

You are commenting using your Google+ 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 )

Connecting to %s