[help] scripted database creation

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.

Re: [help] scripted database creation

Limunski Magarac (limunski_magarac@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, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx

Re: [help] scripted database creation

-> 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

Re: [help] scripted database creation

Limunski Magarac (limunski_magarac@yahoo.com) writes:
> 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.

Ehum, in a web-host scenario, this would mean that the user would have
to call the helpdesk when his database is full. Is that a good thing?

>> > 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.

I now understand even less why you enable guest.

>> > 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?

Nope. It gives the user the same privileges in the database as if
he was dbo, but he will not be dbo. This could matter in Mgmt Studio.
By default all users see all databases. The way to avoid this is to
revoke the permission VIEW ANY DATABASE from users. Then they will
only see the databases master and tempdb and any database they own.

If you are on SQL 2000, this is a little different. But there is
on the other hand the issue that the default schema for the user
will not be dbo.

> 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.

Depends on which version of SQL Server you are using. On SQL 2000 you
use sp_addlogin to create a login, on SQL 2005 you use CREATE LOGIN.
Both are documented in Books Online.

By the way, on SQL 2005 the way to create a database user is CREATE USER.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx

Re: [help] scripted database creation

-> 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