How to configure MSDE (Microsoft Database Engine) without using SQL Server 2000

MSDE is a limited version of the Microsoft SQL Server. In short, it is the Microsoft SQL Server 2000 database engine without any of the fancy UI tools, and with some limitations in the database size and the number of connections. The MSDE database is free, and can be distributed embedded in your own applications or as a small stand alone SQL server. It is ideal for small websites and small businesses with less than 25 simultaneous users. The database is limited to 2 GB of data storage space, but you can easily upgrade it to a full Microsoft SQL Server without any limitations.

The problem of using MSDE is you do not have any fancy UI tools to do the followings:

Manage MSDE Server instances and System Administrator Passwords
Manage Database Users
Manage Database (Create Database, Delete Database)
To install MSDE from command line:

C:\...MSDE\>Setup.exe DISABLENETWORKPROTOCOLS=0 SAPWD="password "INSTANCENAME="myinstance"

To create Login Users:

1> use master
2> go
1> EXEC sp_addlogin ’lars’, ’pass45’, ’larsinge’
2> go
New login created.

To grant Access to the databases:

1> use larsinge
2> go
1> EXEC sp_grantdbaccess ’lars’
2> go
Granted database access to ’lars’.
1> grant all on table_name to lars
2> go

To Create database

C:\> osql –E –S localhost\myinstance

1> use master
2:> go
1> CREATE DATABASE Books
2> go
The CREATE DATABASE process is allocating 0.63 MB on disk ’Books’.
The CREATE DATABASE process is allocating 0.49 MB on disk ’Books_log’.

Other Utilities:

Server Configuration Tool: C:\> svrnetcn.exe

Client Configuration Tool: C:\> cliconfg.exe

For further information:

http://www.codeproject.com/database/ConfigureMSDE.asp