[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

SQL Server tables can't update

Posted on 2011-03-24
8
Medium Priority
?
188 Views
Last Modified: 2012-05-11
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
Comment
Question by:rick_danger
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 19

Expert Comment

by:Rikin Shah
ID: 35205316
Go to Tools > Options > Designers > Uncheck "Prevent saving changes that require table re-creation"

and try.
0
 

Author Comment

by:rick_danger
ID: 35205357
Thanks, but that isn't an available option
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 total points
ID: 35205658
> 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
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
LVL 9

Expert Comment

by:kaminda
ID: 35205731
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
 

Author Comment

by:rick_danger
ID: 35206062
but this viewed on the web, so how will I define a user?
0
 
LVL 9

Expert Comment

by:kaminda
ID: 35213474
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 35215130
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
 

Author Closing Comment

by:rick_danger
ID: 35234766
You were right. when I upsized from Access, it didn't include the indexes.
0

Featured Post

[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
This course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vuln…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

656 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question