Today's question comes from SPG:
My question is, Given that I can automate user creation with ADSI, table creation with SQL commands via ADO, and basic template web page creation with the FileSystemObject... Can I make a whole new database in SQL Server to complete the process? I've got a wide base of unrelated users, so -- even though they use similar web applications -- I'm keeping their data in separate databases. Being able to finish off new user creation automation would be faboo.
I'll give you the Transact-SQL commands to create a database and you can put them into a VBScript or an ASP page. It
shouldn't be hard. You can create a database using the CREATE DATABASE
command. For example, you
might want to create a database for user2
. The syntax would be:
CREATE DATABASE USER2
|
This is the simplest form of the create database. This actually takes the Model
database and
makes a copy of it naming it USER2
. Therefore, any objects you create in model
will appear in any new database you create. Also, any settings in model will be inherited by each new
database. If you are creating multiple databases on the fly (like an ISP might) I'd recommend tuning the
settings in Model
to be just what you want. Especially the
SELECT INTO/BULK COPY
option and the file growth attributes.
In order to create a database, you must be a memeber of the sysadmin
or dbcreator
server role.
The CREATE DATABASE
statement actually has a number of parameters that you can set to customize
your database creation. A more involved example follows:
|
This example provides more detail on the creation of the database. We are naming both the data file and the
log file explicitly. The NAME
and FILENAME
allow us to assign a logical and
physical file name to both the data file (ON
) and log file (LOG ON
). The
SIZE
allows us to set the initial size for the data file and log file. We use the
MAXSIZE
to specificy the maximum size of the file and FILEGROWTH
to set the
increments by which the database will grow.
All numbers will default to megabytes (MB) unless we say otherwise. You can see in the LOG ON
clause I specified MB. You could also ues KB if you wanted a small database. This would probably only be
useful when SQL Server 2000 comes out for the Pocket PC.
You can get even more complicated when you create databases but this is enough for now. Check SQL Server Books Online for details. I hope this is a faboo as you were expecting!
Read Other SQL Guru Questions |