• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 207
  • Last Modified:

SQL Server tables can't update

I have a SQL Server database being used as a back-end for an MS Access database. Initially I created tables which had a prefix of dbo. Access can link to these tables and I can view them and update them. Subsequently I created some more tables and these were created with another prefix of admin. I hadn't realised the significance at the time, but now I can link to them in Access, but I cannot update them. Do I have to do something in SQL Server permissions in order to resolve this problem?
0
rick_danger
Asked:
rick_danger
1 Solution
 
Rikin ShahMicrosoft Dynamics CRM ConsultantCommented:
Go to Tools > Options > Designers > Uncheck "Prevent saving changes that require table re-creation"

and try.
0
 
rick_dangerAuthor Commented:
Thanks, but that isn't an available option
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
> but now I can link to them in Access, but I cannot update them.
actually, either you have permissions to UPDATE rows, or a Primary key is not defined.
please double check
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
kamindaCommented:
I think you have to give permission to that perticular user for new schema. Go to security->Users in the object explorer under the perticular database. Then go to properties select the schema admin from the list shown under Schemas owned by this user. This should solve the issue
0
 
rick_dangerAuthor Commented:
but this viewed on the web, so how will I define a user?
0
 
kamindaCommented:
You can use SQL script instead of using SSMS

GRANT UPDATE ON SCHEMA::test TO testuser

Other permissions you can give as below

GRANT EXECUTE ON SCHEMA::test TO testuser
GRANT INSERT ON SCHEMA::test TO testuser
GRANT SELECT ON SCHEMA::test TO testuser
GRANT DELETE ON SCHEMA::test TO testuser
0
 
Anthony PerkinsCommented:
If Test is a table you cannot do this:
GRANT EXECUTE ON SCHEMA::test TO testuser

It makes no sense.

But as angelIII has already indicated, more than likely you have not defined a Primary Key on the table.
0
 
rick_dangerAuthor Commented:
You were right. when I upsized from Access, it didn't include the indexes.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now