SQL Server 2000/Access 2002 security issue

SQL Server 2000/Access 2002 security issue

Post by Troy » Sat, 21 Jun 2003 03:42:15



I'm having what I *think* are some security issues with an
Access 2002 ".adp" connected to a SQL Server 2000
database. This is a small, single server environment.
From my Access 2002 "adp" client, I can add and delete
tables and nothing else. I can't add/change/delete
records, or change the table design. It does not matter
whom I am logged in as (even admin) or which client I'm
using. When I go to the server and use the SQL Enterprise
Manager, I can manipulate the tables and data any way I
like no matter whom I'm logged in as (regular user or
admin).
On the flip side, I can get into the NorthwindCS sample
from any client and manipulate tables and data any way I
want (the Northwind SQL database is on the same server as
my database). I've logged in as different users from
different clients as a test and not had any problems with
NorthwindCS.
What I've done so far:
1. Created a new adp and database from the client to see
what would happen. I created a very simple table, then
tried to enter data and had the same issues as listed
above.
2. Imported a table from my database to the NorthwindCS
database, then tried to manipulate data in the imported
table. No luck.
3. *Tried* to make sure the security on my database,
tables and views mimics the NorthwindCS security settings.
4. Checked my folder settings on the server (the Access
2002 adp resides there while in development) to make sure
they are not read-only, and that I have sufficient
authority.
5. Moved the adp to the client and tried to manipulate
data without results.
I am pretty new to SQL Server 2000/Windows SBS security
and development, so I don't doubt that there are things I
have overlooked.
I would appreciate any insight that could be given to
resolve this issue. If more information is needed, please
don't hesitate to reply or email (make sure to remove
the "spambuster" thing!).
Thanks in advance.
-Troy
 
 
 

SQL Server 2000/Access 2002 security issue

Post by James Mille » Sat, 21 Jun 2003 23:44:56


Don't know if you done this, but here's something to try.  
Open query analyser for your database and exectue
sp_helprotect.  From the results, verify that the user you
are using to access the database with, 'Grantee', has
a 'ProtectType' of Grant for all 'Action's and all columns
for the table, 'Object', you are trying to manipulate.  If
not, use the Grant command to add those permissions.

-Aaron

Quote:>-----Original Message-----
>I'm having what I *think* are some security issues with
an
>Access 2002 ".adp" connected to a SQL Server 2000
>database. This is a small, single server environment.
>From my Access 2002 "adp" client, I can add and delete
>tables and nothing else. I can't add/change/delete
>records, or change the table design. It does not matter
>whom I am logged in as (even admin) or which client I'm
>using. When I go to the server and use the SQL Enterprise
>Manager, I can manipulate the tables and data any way I
>like no matter whom I'm logged in as (regular user or
>admin).
>On the flip side, I can get into the NorthwindCS sample
>from any client and manipulate tables and data any way I
>want (the Northwind SQL database is on the same server as
>my database). I've logged in as different users from
>different clients as a test and not had any problems with
>NorthwindCS.
>What I've done so far:
>1. Created a new adp and database from the client to see
>what would happen. I created a very simple table, then
>tried to enter data and had the same issues as listed
>above.
>2. Imported a table from my database to the NorthwindCS
>database, then tried to manipulate data in the imported
>table. No luck.
>3. *Tried* to make sure the security on my database,
>tables and views mimics the NorthwindCS security settings.
>4. Checked my folder settings on the server (the Access
>2002 adp resides there while in development) to make sure
>they are not read-only, and that I have sufficient
>authority.
>5. Moved the adp to the client and tried to manipulate
>data without results.
>I am pretty new to SQL Server 2000/Windows SBS security
>and development, so I don't doubt that there are things I
>have overlooked.
>I would appreciate any insight that could be given to
>resolve this issue. If more information is needed, please
>don't hesitate to reply or email (make sure to remove
>the "spambuster" thing!).
>Thanks in advance.
>-Troy
>.


 
 
 

1. Access 2002, Win XP Pro network, DSL, SQL 2000-issues

Since you have two machines, one working and one not, try comparing network
and firewall settings between them. Also try to ping the server from both
machines inorder to take Access out of the equation.

Bill Toleman [MS SQL Support]

This posting is provided "AS IS" with no warranties, and confers no rights.

Are you secure?  For information about the Strategic Technology Protection
Program and to order your FREE Security Tool Kit, please visit
http://www.microsoft.com/security.

2. Front end to informix

3. Install Issues w/ SQL Server 2000 CE and PocketPC 2002 SDK

4. Java Games

5. Transforming Access 2002 SQL to SQL-Server 2000 SQL

6. VB Database access

7. ODBC Access 2002 / SQL Server 2000 Disconnect

8. DTS Editor corrupting mouse driver?

9. Jumping from Access 2002 to SQL Server 2000

10. Access 2002/SQL Server 2000

11. Pessimistic locking with Access 2002/Sql-Server 2000

12. Problem in migrating from Access 2002 to SQL server 2000

13. Access 2002 Project with SQL Server 2000