Using VB Express with SQL Server Express in a lab

I'd like to allow students in a lab to use the Express Manager and T-SQL to create their own databases. I think I've figured out just about everything - but the last step. Here's my thinking on how to do this:

1. Students are power users, not local admin
2. Created a login for the builtin\power users group as follows:
create login [BUILTIN\Power Users] from windows with default_database=[master]
go
exec master..sp_addsrvrolemember @loginame = N'BUILTIN\Power Users', @rolename = N'dbcreator'
go
3. Now students can start the Express Manager and connect to:
localhost\sqlexpress using Windows Authentication
4. After they do, they can open and run a script creating a database and populating it with tables and records.
5. All good up to here... but when trying to access the new database using VB Express (file based, not a remote connection) access is denied.

The problem is in the NTFS file permissions being assigned to the new database mdf and ldf files. Local administrators and the builtin Network Service accounts have permissions, but the student who created the database gets nothing... So the student is denied access to the mdf and ldf files when trying to make a connection to them using VB Express.

I can go in with a different account having admin privileges are assign privileges to power users for the mdf/ldf files after they're created but this isn't what I need (students need to work whenever they want without waiting for an admin to fix the file permissions).

Maybe I'm going about this all wrong If I have to I can insist on students always using VS 2005 instead of VB Express and use connections to a remote database (no problems doing this), but I'm still hoping for an express solution.

I'm new to this forum, so apologies if this is a dumb question (but even if it is - can anyone point me in the right direction )

Thanks

Answer this question

Using VB Express with SQL Server Express in a lab

klaus_b0
I'd do this a little differently.

SQL Server Express (SSE) allows for Xcopy functionality (of MDF and LDFs). In order to use this, and to run as a normal user, you should use the attach style connection string where you specify UserInstance = true and leave off the logical database name and only specify the physical database name.

This will spin up an instance of SSE under the normal user's account. The main instance will be present but hunkers down (memory-wise). The user works with SQL Server, after that, in a normal fashion. When an application you are using with SSE closes, the MDF and LDF are directly copyable. You can move them around just like you would any other file. It's the closing of the app domain that signal SSE to let go of its file lock behavior.

VB Express works with SSE this way natively. The way to get it to work is to create a new project and then choose ADD NEW ITEM. From the dialog, pick a SQL Database. This will add an SSE database directly to the project with the kind of connection string you are looking for.

When a user is done working with a database in this way, it's pretty simple to move it up to a full SQL Server instance. You just move the physical MDF and LDF to the appropriate server and attach them. Then, you just change the connection string in the application to point to the database server and you're done.
Kris Kramer
Also, if you're looking for a TSQL tool to work with SSE when it is running under a run as normal user (RANU) type setting (which is what you do when you use "User Instance = true"), then check out SSEUtil. (Search for it on the web). It works with UserInstance accounts by default. And, it's a great light-weight tool for pushing in SQL via traditional TSQL means. If you rename it to (I think SQLUtil), it will work with the main instance by default.

From a comparison perspective, it's much like SQLCmd, but more more oriented around common tasks.
Using VB Express with SQL Server Express in a lab

Answer this question