September 11, 2003 at 9:41 am
I have set up a database on our SQL Server for someone else in another department using Windows authentication. I want her to have FULL control over this database and ONLY this database. She will be accessing and using the database as a Microsoft Access "project". I created a SQL Server "login" for her that grants her access ONLY to her database and gave her "public" and "db_owner" roles. She has no problem opening the access "project" or any of the existing tables, etc. She can add data to existing tables that already include some data but NOT to blank tables. Other than adding new records, she cannot make any other changes to any of the database objects. What permission(s) am I missing that would give her FULL control over her database?????? I don't think there's anything special I have to do in MS Access -- or is there??? Any help in resolving this issue would be greatly appreciated. Thank you.
Denis W. Repke
Denis W. Repke
September 11, 2003 at 11:52 am
Make her a db_owner in the database.
All the best,
Dale
All the best,
Dale
Author: An Introduction to SQL Server 2005 Management Studio
September 11, 2003 at 11:55 am
Have you created a login account with "Grant access" security and database roles set up for public and db_owner? That is different than creating the User account.
All the best,
Dale
All the best,
Dale
Author: An Introduction to SQL Server 2005 Management Studio
September 11, 2003 at 11:57 am
How 'bout the User account? Have you set her up as a new database user as well? I know you've indicated certain measures but I'm just trying to throw things out.
All the best,
Dale
All the best,
Dale
Author: An Introduction to SQL Server 2005 Management Studio
September 11, 2003 at 11:59 am
Under Database User Properties, have you given her permissions as dbo for SELECT, INSERT, UPDATE, DELETE, etc.?
All the best,
Dale
All the best,
Dale
Author: An Introduction to SQL Server 2005 Management Studio
September 11, 2003 at 12:06 pm
Dale,
Thanks for the reply but I have already made her a "db_owner" and she still cannot make changes to any of the objects. Actually, in taking another look at the issue, it may not be a "permissions" issue as I had first thought. The MS Access error message she gets is "You do not have exclusive access to the database at this time. Your design changes will not be saved." However, she is the only one using the database so why doesn't she have exclusive access??? If I create an Access "project" on my computer to access her database, everything works fine... But she always receives that error message while I never do. Any thoughts?
Denis W. Repke
Denis W. Repke
September 11, 2003 at 12:07 pm
Is she on a Windows 98 machine?
All the best,
Dale
All the best,
Dale
Author: An Introduction to SQL Server 2005 Management Studio
September 11, 2003 at 12:14 pm
OK. Got it. In your Access App, go to Tools/Options/Advanced and make sure that the Default Open Mode is set to Shared. Let me know...
All the best,
Dale
All the best,
Dale
Author: An Introduction to SQL Server 2005 Management Studio
September 11, 2003 at 12:19 pm
Dale,
The "Default Open Mode" setting is ONLY available for Access databases (i.e., MDBs). It is NOT an option for projects (i.e., ADPs) -- the option does not exist under Tools, Options, Advanced. Any other ideas.
Denis W. Repke
Denis W. Repke
September 11, 2003 at 12:20 pm
Dale,
I forgot to answer your other question... No, she is not on a Windows 98 machine. She's running W2K Pro with Office 2000.
Denis W. Repke
Denis W. Repke
September 11, 2003 at 12:22 pm
We might be out of luck with Projects. I think this is by design. Hang on...
All the best,
Dale
All the best,
Dale
Author: An Introduction to SQL Server 2005 Management Studio
September 11, 2003 at 12:30 pm
OK. Got it. Just as I thought. You have to give a copy of the project to each developer. See article:
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q283228
Let me know if that answered your question.
All the best,
Dale
All the best,
Dale
Author: An Introduction to SQL Server 2005 Management Studio
September 11, 2003 at 12:48 pm
Dale,
No, I'm afraid that article did not help. It's talking about saving "Access objects" and primarily deals with Access databases (not projects). Also. she's using Access 2000 NOT 2002 which is what the article applies to. The error message she receives is from the SQL Server objects (tables, stored procedures, etc.) NOT the MS Access objects that the article refers to (i.e., reports, forms, etc.). And, to reiterate, if I create an Access project on my computer to access her database, I do NOT have any of these problems.
Denis W. Repke
Denis W. Repke
September 11, 2003 at 12:50 pm
OK. I'll keep looking and testing our ADP here.
All the best,
Dale
All the best,
Dale
Author: An Introduction to SQL Server 2005 Management Studio
September 11, 2003 at 12:54 pm
What version of SQL Server do you have?
All the best,
Dale
All the best,
Dale
Author: An Introduction to SQL Server 2005 Management Studio
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply