I just found this site today and posted this problem in chit chat I think this may be a more appropraite forum...
I've got an application that works off an SQL 7/2000 database. For our clients who do not have an sql server we want to be able to set up our database with msde. I know how to install msde but cannot find the right information on how to install our database and connect to the msde. I also need to setup the user that our application logs in under. The application looks for a filedsn that is configured with our hardcoded username and password. I know how to setup the dsn but come back to the same issue of setting up our username and password so that it is the dbowner of the database.
I prefer to create this setup myself so that I can support any new fetures that may be required but would be willing to look at any 3rd party solution.
Thanks for any help with this.
As far as the User and DSN are concerned, we have a project with a similar setup. To add the user, I altered our install package to run some SQL to accomlish this. Basically, I used OSQL to do the following three stored procedures:
-- Create the login with the current database as the default
EXEC sp_addlogin User, 'Default Database', Database
-- Grant the user access to the currently connected database
EXEC sp_grantdbaccess User
-- Assign them as a db_owner role member in the current database
EXEC sp_addrolemember 'db_owner', User
There's more error-checking and setup involved in the install script, but that's basically what is done to get the user setup.
We do the same kind of thing. To install the database you can use the attach DB stored procedure. This is the fastest way since it just attaches your DB files to the server. But if the user has an existing SQL server 7.0 installation if the sort order is different you will run into trouble.
To get around these issues we create our DB entirely from SQL scripts. We install these with the app. When the app runs first time we run the scripts to create the DB. In the scripts we add the application user to the DB/SQL Server. We then import the data into the DB using DMO. We generate the import data and the DB scripts from a template DB using Enterprise manager.