Solved

SQL Server tables can't update

Posted on 2011-03-24
8
175 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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Suggested Solutions

This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…

816 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

8 Experts available now in Live!

Get 1:1 Help Now