Solved

SQL Server tables can't update

Posted on 2011-03-24
8
171 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
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 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 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
 
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
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

758 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now