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.
| Author |
Topic |
|
morriscm
Starting Member
2 Posts |
 Posted - 2005-08-18 : 12:36:43
|
| 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 admin2. Created a login for the builtin\power users group as follows:create login [BUILTIN\Power Users] from windows with default_database=[master]goexec master..sp_addsrvrolemember @loginame = N'BUILTIN\Power Users', @rolename = N'dbcreator'go3. Now students can start the Express Manager and connect to:localhost\sqlexpress using Windows Authentication4. 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?)ThanksMike Morrison |
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2005-08-18 : 14:01:13
|
| Mike,If you grant Power Users access to the model database, then any new database created thereafter will be created with the same access. Of course, by doing this, you are giving every user access to each other's databases. If that's not acceptable, then you'll have to grant individual permissions after creation.By the way, my personal preference is not to set anybody with master as their default database. The CREATE DATABASE command can be issued from any database, and I'd rather not have people sitting in master in case they forget to change which database they are using before they try to execute anything. So my normal setup is to default people to Pubs unless I can set their default to something more meaningful for them.---------------------------EmeraldCityDomains.com |
 |
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2005-08-18 : 14:05:02
|
| A different approach would be rather than having them run the script to create the database, have them execute a small VB application that connects with sufficient permissions to create the database AND grant permissions to them. If you are trying to teach them the syntax of the CREATE DATABASE command, you could have it display the syntax that it will use, or you could have them have to type in the syntax for the CREATE DATABASE in your application, but still the application connects and executes it with the higher permissions.---------------------------EmeraldCityDomains.com |
|
|
|
|
|
|
|
|
Using VB Express with SQL Server Express in a lab