[help] scripted database creation

[Limunski Magarac]

Hi all :)

My apologies if I posted in the wrong groups, but I just jumped
in MS SQL waters, so any guidance will be appreciated.

What I'm trying to do is the following process:

[1] present operator with a web page (script)
[2] once filled with db name and username, script will create
..sql file
[3] osql.exe will be called with, i presume, -i file.sql and
create a database

I have limited SQL knowledge, but I got the information from
Enterprise Manager when I ran 'All Tasks -Generate SQL
Script' on how the .sql file should look like.

I realized what are the commands that would create a fresh DB
(I ran this for newly created DB), and figured my .php script
should create such a file.

It's fairly basic, and I'm almost sure all of you know how
outputed .sql file looks like, but anyway here it is:

Script is called with parameters 'six' as database name and
'magarac' as user name:

---------------------------------------------------------------
IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name
= N'six')
DROP DATABASE [six]
GO

CREATE DATABASE [six] ON (NAME = N'six_Data', FILENAME = N'E:
\Databasepath\six_Data.MDF' , SIZE = 1, MAXSIZE = 20,
FILEGROWTH = 10%) LOG ON (NAME = N'six_Log', FILENAME = N'E:
\Databasepath\six_Log.LDF' , SIZE = 1, MAXSIZE = 20, FILEGROWTH
= 10%)
COLLATE SQL_Latin1_General_CP1_CI_AS
GO

exec sp_dboption N'six', N'autoclose', N'false'
GO

....

use [six]
GO

if not exists (select * from dbo.sysusers where name =
N'guest' and hasdbaccess = 1)
EXEC sp_grantdbaccess N'guest'
GO

if not exists (select * from dbo.sysusers where name =
N'sinisam')
EXEC sp_grantdbaccess N'magarac', N'magarac'
GO

exec sp_addrolemember N'db_owner', N'magarac'
GO
---------------------------------------------------------------

I managed to get an exact replica of .sql file that Enterprise
Manager created. It leads me to believe that this way of
automated database creation is indeed possible.

Really sorry for making you to go through all this text, but
after I get a green light on this from you guys, I'll have a
bit more problematic question.

Is there any reason why this should not be used, or would fail?

Thanks in advance :)
P.S.
Just as a heads-up, next part of my problem is automated
creation of new MS SQL server login to use with new DB.

[Erland Sommarskog]

Limunski Magarac (li**************@yahoo.com) writes:

CREATE DATABASE [six] ON (NAME = N'six_Data', FILENAME = N'E:
\Databasepath\six_Data.MDF' , SIZE = 1, MAXSIZE = 20,
FILEGROWTH = 10%) LOG ON (NAME = N'six_Log', FILENAME = N'E:
\Databasepath\six_Log.LDF' , SIZE = 1, MAXSIZE = 20, FILEGROWTH
= 10%)
COLLATE SQL_Latin1_General_CP1_CI_AS
GO

The SIZE and MAXSIZE values, from where did you get these? Out of
thin air? Or is there is a limit of 20 MB in the terms of service?
20 MB is quite small size. Not the least for the log file. And if
20 MB is indeed the size, there is little reason to fiddle with
autogrowth - create it to 20 MB directly.

Also, I would question the choice of collation. Given your name and
that you appear from post from Germany, I think you should pick a
different collation.

exec sp_dboption N'six', N'autoclose', N'false'
GO

Good!

if not exists (select * from dbo.sysusers where name =
N'guest' and hasdbaccess = 1)
EXEC sp_grantdbaccess N'guest'
GO

Eh? Any particular reason you enable guest? Particularly at a web
host, I would not recommend this.

if not exists (select * from dbo.sysusers where name =
N'sinisam')
EXEC sp_grantdbaccess N'magarac', N'magarac'
GO

exec sp_addrolemember N'db_owner', N'magarac'
GO

Hm, wouldn't be better to make the user the owner of the database?


--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Limunski Magarac]

-Erland Sommarskog ->

Hi Erland,

Thank you for your reply.
Apologies for not responding sooner, but I had a lot on my mind
during weekend time.

The SIZE and MAXSIZE values, from where did you get these? Out of
thin air? Or is there is a limit of 20 MB in the terms of service?
20 MB is quite small size. Not the least for the log file. And if
20 MB is indeed the size, there is little reason to fiddle with
autogrowth - create it to 20 MB directly.

Actually, I created one database just the way I usually do and
then started 'All Tasks -Generate SQL Script' to see how .sql
file looks for the newly created databases.

20 MB is just a default size. Many times it's not needed to
increase the size, but if it's necessary, I can always do it
manually.

Also, I would question the choice of collation. Given your name and
that you appear from post from Germany, I think you should pick a
different collation.

It is the correct one, but I'm glad you noticed :)

if not exists (select * from dbo.sysusers where name =
N'guest' and hasdbaccess = 1)
EXEC sp_grantdbaccess N'guest'
GO

Eh? Any particular reason you enable guest? Particularly at a web
host, I would not recommend this.

Access is granted via group membership. It has 'public' role
membership. But I'll look into this further, thanks for the
heads-up.

exec sp_addrolemember N'db_owner', N'magarac'
GO

Hm, wouldn't be better to make the user the owner of the database?

Isn't this the exact thing this line does? Sets the user as the
owner?
So in the end, I guess this can be used to script a database
creation?

When the script is called with DBname and username, it should
create the database and a new SQL server login (and set default
database for this login to DBname).

I'm afraid I really do not know how to script a creation of a
new login. I've managed to go this far with scripted creation
of database, and with your help I went even further.

But if you have any advice on adding new SQL logins, link or
any other hint, I'd be more than happy to do more researching.

Thank you for your time,
Six

[Limunski Magarac]

-Erland Sommarskog ->

I have no words to thank you!

I think I'm gonna stalk you now and see if there's anything you
need help with :)

Seriously, thanks for pointing out stuff I took for granted,
too.

Much appreciated,
Six