Create SQL database with C#

[orenbt78]

Hi,

I am trying to:
1. Create a SQL database (I am working with SQL 2005 Express)
2. with a C# code
3. when the user is not the computer administrator.

I have managed to create the database file (code below). I am not sure
it is the right way.
Can you take a look please?
I would like to either create a password for these database or a
special user so only my
software will be able to control it (change data). How do I do that?


tmpConn.Connect ionString = "Data Source=(local); DATABASE =
master;Integrat ed Security=True; user instance=true";
sqlCreateDBQuer y = " CREATE DATABASE " + DBParam.Databas eName +
" ON
PRIMARY "
+ " (NAME = " +
DBParam.DataFil eName +", "
+ " FILENAME = '" +
DBParam.DataPat hName +"', "
+ " SIZE = 5MB,"
+ " FILEGROWTH =" +
DBParam.DataFil eGrowth +") "
+ " LOG ON (NAME =" +
DBParam.LogFile Name +", "
+ " FILENAME = '" +
DBParam.LogPath Name + "', "
+ " SIZE = 1MB, "
+ " FILEGROWTH =" +
DBParam.LogFile Growth +") ";
SqlCommand myCommand = new SqlCommand(sqlC reateDBQuery, tmpConn);
try
{
tmpConn.Open();
MessageBox.Show (sqlCreateDBQue ry);
myCommand.Execu teNonQuery();
MessageBox.Show ("Database has been created successfully!",
"Create
Database", MessageBoxButto ns.OK, MessageBoxIcon. Information);
}
catch (System.Excepti on ex)
{
MessageBox.Show (ex.ToString(), "Create Database",
MessageBoxButto ns.OK, MessageBoxIcon. Information);
}
finally
{
tmpConn.Close() ;
}

[Erland Sommarskog]

Re: Create SQL database with C#

orenbt78 (orenbt78@googl email.com) writes:

I have managed to create the database file (code below). I am not sure
it is the right way.
Can you take a look please?

I guess that if the database gets created that it works. The only thing
to consider is maybe the initial sizes. 5 MB is a quite small database.
Then again, I don't really know what you will put into it.

I would like to either create a password for these database or a
special user so only my
software will be able to control it (change data). How do I do that?

You could use an application role. You add users to the database without
any privileges, and then the application users sp_setapprole to set the
application role, to which you have granted all necessary rights.
To activate the application role, you need a password.

But note that this does not prevent users from side-stepping your tool,
it just makes it more difficult. If you give users the password, they
can use sp_setapprole from Mgmt Studio. If you don't give them the
password, you need to hide in the application, in which case be found
for anyone who wants. Or the connection can eavesdropped. (The password
can obfusticated on the wire, but that does not help.)

And in any case, anyone can just copy the database files and attach
them on a server where they have admin rights and do whatever you want
with it.

So while you cannot prevent this, the application role can still serve
the purpose to tell people to keep out, or the warranty will be voided.
But this latter is something you also include in a license agreement.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx