Solved

SQL Server tables can't update

Posted on 2011-03-24
8
179 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 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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
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

Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

Question has a verified solution.

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

When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
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…
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …

734 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