MS SQL 2000 to MSDE - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

MS SQL 2000 to MSDE

Author

Topic

MaverickUK
Yak Posting Veteran

89 Posts

Posted - 2004-08-05 : 11:47:40

Hi guys

I'm currently in the process of re-packaging a web based application we use (ASP.NET/C# front-end with a MS SQL 2000 back-end) so that we can give it to other organisations to use.

To make it portable, we're moving the MS SQL 2000 database into MSDE, so that it maintains most of the functions, without the person installing it needing MS SQL 2000.

As I've never done anything like this before, I'm attempting to understand the best way to install the MS SQL 2000 database into MSDE on the PC it'll be installed on.

The current method I'm developing is as follows:
1) MSDE is installed on PC
2) VB application talks to MSDE through SA account, then runs various querys
Query 1> Creates database
Query 2> Restores a backup of our MS SQL 2000 database, onto MSDE
Query 3> Creates a new user
Query 4> Assigns execute permissions of new user to relvent stored procedures.

Is this the best way to do things? I suspect not, but as I've no prior experience of such an operation, I'm attempting to feel my way along.

Any suggestions are most welcome!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-08-05 : 15:00:06

The best way to put the database into MSDE is to use osql.exe at the command line and use RESTORE command.

Why is your application using sa? This the number one no no for security.

Tara

MaverickUK
Yak Posting Veteran

89 Posts

Posted - 2004-08-06 : 04:09:58

Because the VB app is for the inital installation, at which time no other user accounts exist. So the VB app talks to MSDE using the SA login.

The actual .NET/C# application runs through stored procedures with their own username.

So in short, you are argreeing with the method I am using?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-08-06 : 12:25:09

No. I wouldn't recommend running a VB app to install a database. The database should be restored using osql.exe on the database server.

Tara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources