We seem to have a problem with permission levels and connecting to an MSDE (MSSQL) server. If the user is under the Domain Admins group, the the access projet (front end) will open correctly and connect to the data server. If they are not part of that group then the front end can ever establish a file to the database server. We do not want to make all the users Domain Admins, so is there a way to make MSDE let them trough even though they are on a lower level.
I've done many tests, and also tried many things. I've even went to the extent to give Full Control to the whole MSSQL folder in program files for Everyone. I have made sure that the database file itself inherieted it's parents security settings, which were what I had just described.
Any ideas how how to make MSDE let anyone connect? Thanks in advance!
Domain admins are allowed into the database by virtue of the fact that they are included in the Local Administrators group in the machine running MSDE. So, the quickest thing you could do is make your users local admins, rather than domain admins. In order to reduce their permissions further, you will have to settle what you want the users to be able to do. Local Admins can do anything to the instance of MSDE (configure settings, drop/add databases, forget to backup their data, etc.), but then, these are MSDE users, so they are usually only affecting themselves.
Thanks again for replying so quickly! I am a little unsure what you mean by local admin though. It is an NT server, by the way. There is no group called Local Admin. I tried the group Administrators, but that didn't work. Do you mean on each workstation?
We may have to back up a step here. MSDE is usually installed on workstations (my assumption, my bad). You have one central server, with various workstations connecting to the instance of MSDE on that server, then? Now we have a bit of a rat hole. Welcome to the world of SQL Server permissions. In other words, not much is going to be automatic here. Here is how you will likely have to start:
1) Create a domain group for the users who need to use the application.
2) On the server, with a command window, run
osql -E (-S servername\instancename)
NOTE: You may need to supply the MSDE instance name. If you get a numbered prompt, you are in.
3) switch to the application database by running the following:
4) Run the following commands:
1>exec sp_grantlogin '(domain\new group from above)'
2>exec sp_grantdbaccess '(domain\new group from above)'
3>exec sp_addrolemember db_owner, '(domain\new group from above)'
Once you have done all that, you should be able to run without making people domain admins. I am not too wild about having you grant db_owner to the users, but they would have had sysadmin otherwise, but that is a long debate between programmers and DBAs that I probably do not need to invoke here. Good luck.
I thank you very much for getting back with me. I apoligize for not replying in a few days, as I was vey busy. The method you described is what I was basically looking to do. I did exactly what you said, but it did not seem to work. I'm afraid I've done it wrong. I just wanted to make sure of the notiation you meant for me to use when typing it. Let's say for example:
1. the user group is called SQL Users
2. the domain is called DOMAIN
3. the server is called SERVER1
4. the database is called IT Help Desk
Also...the single quotes...in the command prompt, there are open and close ones. The one that is usually used for both (located near the enter key on he keyboard) shows up as a close single quote. So what should be used? An open then close one? Both closed? Just wondering. If anyone else knows, feel fee to post as well.
It's not really an error message. It's just not able to connect to the server because of permission settings. I know this, but I can't get the server to let anyone connect. I tried without the pathrenthesis, but it still didn't work. TO be more detailed, when in MS Access, and trying to open a form (the point at which it needs to talk to the server) it gives me a run time error 18456, which says that the database could not log onto the server and to verify that the log on info is correct. I know that it's the groups that is the issue, because when I add the user that I am currently logged on as in the Domain Users group, it works fine. I think you were going in the right direction. I just wish MSDE had an interface to work with. I hate this stupid command line stuff. Too much coding. I'm not a professional programmer, altough I have taken VB a few years ago and know most of that. Other then that, web programming is all I know. Anyways, I'm heaing home for the holidays, this project isn't top priority, so I'll work on it when I get back. If you have any other ideas, please let me know. If you want to contact me directly, please email firstname.lastname@example.org, thanks! Happy holidays!
I had a similar problem with XP. If using Windows XP your (regular) local user will have very restricted user rights concerning writing to the registry. In addition there may be problems using the .NET framework and MSDE.
It may not be enough for installing or even using the MSDE. The MSDE requires writing to the registry, therefore the user does not need administrator rights but local user rights of a poweruser. We had the same problem. Have a look to the MSDE installation guide and/or readmes and the security settings of your .NET framework. This was helping us, the error messages did not lead to the right conclusions.