Don't understand my permissions to my SQL Server DB

  • Thread starter Thread starter Larry Woods
  • Start date Start date
L

Larry Woods

I have an offsite SQL Server (IP address) database. I can get to it just
fine using Enterprise Manager; make changes to tables, add fields, etc.
Then I open my Access adp, which is pointing to the same SQL Server database
I can view any of the tables but the tables are "read only." I can't do any
updates, etc. My connection has the same userid/password as I am using
through EM to get the database.

What am I missing?

TIA,

Larry Woods
cross-posted to sqlserver.programming
 
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Are you using Windows Authentication?
-----
Have you checked the Logon account permissions for each user? Does each
user have permission to access the database?
-----
Does each user have UPDATE and/or ADD permission checked on each
table/view? Better yet, is each user in the Role that has permissions
to the tables?
-----
Test if your EM user_name() is the same as your Access connection
user_name(). In EM open the Query Analyzer and type in:

print user_name()

and execute that statement.

In the .adp create a stored procedure (SP) that returns the user_name():

CREATE PROCEDURE test_user
AS
SELECT user_name()

Open (run) that procedure in the .adp.

The user_name returned by both experiments should be the same. Does the
UID in the connection string have the same name as the name returned by
the SP? (Note: dbo is sa as the UID, e.g.: UID=sa.)
-----
Try dropping the connection & then reconnecting.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQSEhkoechKqOuFEgEQK5ngCeMZU1kYZ6wwlrCSRHaEbXn0d0yd4AoPGl
TJNX6Ev2HJfVhyfccc7wwZ3G
=iPKA
-----END PGP SIGNATURE-----
 
Back
Top