Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 319
  • Last Modified:

when is an owner not an owner

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
juststeve
Asked:
juststeve
  • 4
  • 3
1 Solution
 
stephendlCommented:
Can you create new records using query analyser when you log in as your 'non-sa' account?
0
 
juststeveAuthor Commented:
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
 
stephendlCommented:
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
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
juststeveAuthor Commented:
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
 
stephendlCommented:
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
 
stephendlCommented:
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
 
juststeveAuthor Commented:
yep...belongs in the Access area...thankx.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now