Solved

when is an owner not an owner

Posted on 2002-04-10
7
312 Views
Last Modified: 2012-05-04
I've set up my 'non-sa' account as owner of a particular db (sql2k sp2). I was probably logged in as sa when I created the db using DTS from an existing db.

When I connect from my workstation using MSAccess2k via an odbc connection using the non-sa account i find that I don't have ability to create new records. If I connect as sa i can.

looking at EM's Users for that db my non-sa login has checks for 'public' and 'db_owner'. I didn't (don't) think any of the other roles are anything more than subsets of db_owner, yes? So why isn't being owner good enough for Access.

My primary concern here isn't so much connecting via Acess...the db exists only to give stuff to IIS/web pages. I'm wondering if this indicates other things that may bite my butt going forward.

thankx
--steve...
0
Comment
Question by:juststeve
  • 4
  • 3
7 Comments
 

Expert Comment

by:stephendl
ID: 6936282
Can you create new records using query analyser when you log in as your 'non-sa' account?
0
 

Author Comment

by:juststeve
ID: 6936499
Yep...it's just linked tables through Access via the non-sa account that's having a problem. SA/Access works.

I have web pages using the same non-sa, DSN-based connection able to insert and update.

thankx
--steve...
0
 

Expert Comment

by:stephendl
ID: 6936588
But it works in query analyser? Hmmm.

It sounds like your connection to the ODBC's. The web stuff that works, is it asp? Are you getting any error messages in Access? If so, where, when and what?
0
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 

Author Comment

by:juststeve
ID: 6936687
No error messages...just a beep if trying to edit a cell and the  'insert record' button at the bottom of the frame is grayed out

[...pause...]

jeeze...i've just gone over there to reproduce whatever dialog box response i'd gotten when typing in the bottem row...and _now I can't reproduce any of the behaviors. [sigh] later today I'll take smoe time to create a new db from scratch (client-side) and see if the read-only issue reoccurs.

The web stuff is asp...uses a connection string like:
  dsn=myDb;uid=not_sa;pwd=thatpw;

When linking the table from MSAccess I point to the same DSN, use the same account/pw and get _only_ read-only access to the same tables that the web pages are able to insert/update.

I suppose the asp route adds the ADO layer but the question remains as to why Access would allow the account only limited access.
0
 

Expert Comment

by:stephendl
ID: 6936768
But it works in query analyser? Hmmm.

It sounds like your connection to the ODBC's. The web stuff that works, is it asp? Are you getting any error messages in Access? If so, where, when and what?
0
 

Accepted Solution

by:
stephendl earned 50 total points
ID: 6936780
Sorry about that... hit refresh on accident...

OK... if you can replicate, tell me what happens.

Are you using a 'Trusted connection' in access? Access isn't really my forte, it has too many strange behaviours and quirks for me!... did you try posting into the Access topic area?
0
 

Author Comment

by:juststeve
ID: 6987728
yep...belongs in the Access area...thankx.
0

Featured Post

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.

Question has a verified solution.

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

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

920 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

12 Experts available now in Live!

Get 1:1 Help Now