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 |
|
PDC
Starting Member
4 Posts |
 Posted - 2007-02-14 : 11:42:45
|
| I'm sure this one will win "dumb newbie question of the day". I recently installed SS2005EE on a laptop (Windows XP). I also installed the associated Management Studio Express as the database "creation" environment. When I started to build my first, simple database, one of the first things I tried to do was to change the default file storage location. I have certain folders and subfolders I've created in the past for organization and convenience. I wanted the database files to be stored in a particular such folder, instead of the default location.In the "New Database" window, where the default "Path" is specified, I clicked the browse button. A window called "Locate Folder" pops up, looking like Windows Explorer. Unfortunately, the desired folders I've created in the past are not shown. I can see them in Windows Explorer, but it's like the SSE folders explorer can't see them. What gives? Should I take my SSE program to my optometrist to be fitted for glasses? (Kidding). Any help will be greatly appreciated! |
|
|
mikewa
Microsoft SQL Server Product Team
84 Posts |
Posted - 2007-02-14 : 12:35:50
|
| SQL Express doesn't need glasses, it needs permission. SQL Express access folders using its Service Account, which is by default Network Service. You can only access and/or created database in folders that the Network Service account has permissions to use.Network Service is a low permission account and is not given permission to folders by default, particularly folders within a specific user profile, such as anything under your My Documents folder. If you want to store database files any where other than the specified Data directory, you will need to provide permissions to that folder to the SQL Express Service account.In general, you should be creating your databases in a standard location. SQL Express is not a normal user process such as Word or Excel, it is a Service that runs all the time your computer is running and has it's own user accounts. Typically you define a Data directory (or use the default) and then put all your database there. You can then secure that directory so that most users do not have physical access to the folder, which requires that they access your data through your SQL service and can not make their own copy.Regards,Mike WachalSQL ExpressThis posting is provided "AS IS" with no warranties, and confers no rights.Use of included script samples are subject to the terms specified athttp://www.microsoft.com/info/cpyright.htm |
 |
|
|
PDC
Starting Member
4 Posts |
Posted - 2007-02-14 : 13:35:47
|
| Many thanks...I really appreciate your help. Locating all the databases in a single location (folder), as you suggest, is exactly my intention. I just need (for certain reasons) for such destination folder to be one other than the default.How to I go about finding this "Network Service" fellow so that I might bestow the requisite permission? I've spent some time looking through the Books Online, and I've found references to "Network Service" here and there, but nowhere have I found instructions on how to bring the Network Services up onto the screen and tell it that it has permission to view certain folders.Again, many thanks! |
|
|
|
mikewa
Microsoft SQL Server Product Team
84 Posts |
Posted - 2007-02-14 : 14:42:10
|
| You assign permissions at the folder (file/service/etc.) level, not at the user level. Go to the folder where you want to store your databases, pop open the Properties dialog and switch to the Security tab. You'll be able to add users (in this case Network Service) and assign the specific permissions that the user should have for that folder. Take a look at the existing Data directory as a template for what permissions to give.Once you've assigned permissions, you should open up Management Studio Express, open the Server Properties Dialog and switch the othe Database Setting page. From there you should be able to set the path for the 'Database Default Locations' to your new folder, which will ensure that all future databases you create will go there automatically. Unless you want to split your data and log files, use the same path for both settings.Regards,Mike WachalSQL ExpressThis posting is provided "AS IS" with no warranties, and confers no rights.Use of included script samples are subject to the terms specified athttp://www.microsoft.com/info/cpyright.htm |
|
|
|
PDC
Starting Member
4 Posts |
Posted - 2007-02-14 : 16:03:16
|
| Mike, I do appreciate your help, and I'm sorry for making this seem so difficult. I'm beginning to think that my versions of Windows XP and Mgmt Studio Express both came from some weird alternate universe where everything's different. Cases in point..."Go to the folder where you want to store your databases, pop open the Properties dialog and switch to the Security tab. You'll be able to add users..." Simple enough. Except that no matter what folder in my Windows Explorer I select, when I right-click, and open "Properties", I have no "Security" tab. Zip, zilch, nada. "Once you've assigned permissions, you should open up Management Studio Express, open the Server Properties Dialog and switch the othe Database Setting page..." Again, simple enough. But, in my alternate-universe version of MSE, I can find nothing called "Server Properties" or anything that's a Database Setting Page. I have clicked on every button, icon, and menu option in sight until smoke was pouring from my mouse, but to no avail.I have two SS2005EE books at my side, and I'm pretty darn good with Google. I have diligently tried researching this problem on my own to avoid wasting anyone's time on this forum, and I genuinely appreciate your responses. My situation is (or so I thought) the simplest of all: It's just me and a laptop. I'm the only one who uses this computer. No multiple users, no remote access issues, no multiple account-type issues. Can SS2005EE only operate within a complex environment of multiple and remote users, such that it falls apart in the face of simplicity? I just wanted to create, program, and manage a few simple databases on my computer for my own personal use. Very sorry for venting, but I do feel better now. I know the answer's out there, and I'm sure it's simple. I'll keep researching on this end, and in the meantime, any further help will be greatly appreciated. Thanks! |
|
|
|
mikewa
Microsoft SQL Server Product Team
84 Posts |
Posted - 2007-02-14 : 16:53:58
|
| Actually, the whole "where's the security tab" issue has been bugging me for awhile, so I went and looked it up...When you're using XP Pro in Workgroup mode it is automatically put into "Simple Sharing Mode" which means you don't get the Security tab. You can turn this off using the steps documented in the KB article at http://support.microsoft.com/kb/307874. If you are running XP Home, there is no way to turn off Simple File Sharing so you are pretty much stuck with the way permissions work by default. (Why? XP Home just isn't about fine grain permissions control or doing server based database development.)As far as Management Studio, I should have been more specific. Once you connect to your server, right-click on the Server name and click Properties from the context menu. That should open the Server Properties dialog and show you a list of pages, one of which is Database Settings. You'll find that many usefull things are tucked away on the context menu in products so it's always usefull to right-click on things to see what is there.Regards,Mike WachalSQL ExpressThis posting is provided "AS IS" with no warranties, and confers no rights.Use of included script samples are subject to the terms specified athttp://www.microsoft.com/info/cpyright.htm |
|
|
|
PDC
Starting Member
4 Posts |
Posted - 2007-02-14 : 17:37:44
|
| Mike, you Da Man! First, I AM running XP Home, and your explanation squares perfectly with the things I was seeing in working with these folders.Second, with respect to Mgmt Studio, I followed your instructions, and with a few mouse clicks and keystrokes, I had gotten the job done (by the way, I specified the SAME folder for both the DATA files and the LOG files, which seems to be a good idea).I'm certainly at the very beginning of the learning curve on all this SS2005EE stuff, but perhaps sometime down the road I'll be able to contribute something helpful in this forum to a puzzled newbie.Thanks again, Mile. Over and out for now....gotta go build a database! |
|
|
|
|
|
|
|
|
SSE needs glasses?