February 13, 2011 at 3:47 pm
first of all, i´m a newbie...so I may be doing something completily stupid. I'm doing some tests with ODBC and Access.
I've setup SQLEXPRESS with mixed mode on my pc.
I used standard instalation and created a table with 2 columns, put insome test data.
I setup two ODBC channels, one with windows validatio and another with SQL validation.
The Security Tab has all the default users and 3 others, sa, user1, and pc\user.
The pc\user is configured in the User Mapping for my test db with the dbo schema, membership db_owner and public, does not let me choose datawriter and datareader, gives error for dbo schema, I think this is part o the problem.
Under the test db, I have the user dbo, and user1. Cannot add pc\user, gives error saying that is configured in dbo.
User dbo has pc\user as login. Database Role is owner, if try to put datawriter and datareader gives error, cannot use special principal dbo.???? Schemas owned is all blank.
User "user" has login as user, owner, datareader and writer owned schemas and roles. In Securables tab, has alter, control, delete, execute, insert, select, take ownership, update selected.
If I use either ODBC channel to link the table in access, thru windows logon wich should use the pc\user login, or thru the SQL login wich uses the user login, I see the tables but have no permission to write, read, insert or delete.
I'm at a loss, I've tried several combinations of permissions and schemas for the user, but nothing.
Since I cannot delete the dbo, I cannot change the roles of the dbo, gives errors, I don't know what to do.
I would appreciate some help.
thanks a lot
February 16, 2011 at 3:00 pm
The issues you are dealing with involve logins and database users and how they are mapped as well as roles, schemas and permissions.
If you are going to be doing anything more than casual goofing around with SQL server you'll need a better understanding of how all these entities relate and interact. I suggest you purchase a good book on SQL Server and start learning the fundamentals. I can recommend SQL Server 2008 unleashed but there are many good ones out there.
The probability of survival is inversely proportional to the angle of arrival.
February 16, 2011 at 3:41 pm
thanks for the reply but...
I'm not just goofing around, I do some programming work in Access and I'm trying some mixed table configurations.
I've used SQL services that were not installed by me, with success, no problema connecting both services.
In this new installationfrom scratch, I just can get it to R/W.
I have already 3 books on SQL, bible, begginers guide, MS-SQL for dummies...lol.
but all these go from the standard instalation in windows login or mixed mode straight into creating tables, selects and programming. The part related to roles, permissions and schemas are not covered.
Do you know of a site, book, that covers details about permissions.
I administered Windows server security and I've done in this situation, I what I think is logic. But the errors on modifying the dbo and creating/modyfing de pc\user has prevented me from completing that logic.
To me, it does not seem logic that I can´t create a new user on the database related to the pc\user because it´s already referenced in the dbo user...
Thanks for any further help
February 17, 2011 at 2:27 pm
when you use ODBC to link Access to SQL Server tables, you MUST choose a primary key in the SQL table or they will come up RO.
February 18, 2011 at 4:37 pm
thanks, that solved the problem
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply