473,326 Members | 2,255 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

MS SQL linked tables vs. Pass Through Queries

Hi,

I am working on an Access application where the author used SQL back
end via linked tables.

I wrote some general functions that would execute pass through queries
and was going to call them for all new DB interaction.

They work fine as long as I go into SQL Server and give the User
Select, Update, Insert and Delete privileges. However this Access
application using internal queries is running fine with virtually every
table in the SQL database not offering ANY privileges to this user.

As this is a large organization I would rather not go thru the "red
tape" with the DBA staff to change numerous SQL DB settings to support
pass through so I will probably continue what the author started.

My question is... what is the difference? How does Access through
linked tables successfully Select, Update, Insert and Delete when the
user ID used to connect doesn't have any privilges according to SQL
Server?

Thanks

Nov 13 '05 #1
8 7738
"ZRexRider" <je****@ptd.net> wrote in message
news:11**********************@c13g2000cwb.googlegr oups.com...
Hi,

I am working on an Access application where the author used SQL back
end via linked tables.

I wrote some general functions that would execute pass through queries
and was going to call them for all new DB interaction.

They work fine as long as I go into SQL Server and give the User
Select, Update, Insert and Delete privileges. However this Access
application using internal queries is running fine with virtually every
table in the SQL database not offering ANY privileges to this user.

As this is a large organization I would rather not go thru the "red
tape" with the DBA staff to change numerous SQL DB settings to support
pass through so I will probably continue what the author started.

My question is... what is the difference? How does Access through
linked tables successfully Select, Update, Insert and Delete when the
user ID used to connect doesn't have any privilges according to SQL
Server?


The links must have been created using an account that does have privileges and
that account info was saved as part of the link definitions. The authorities on
the server DO prevail over anything in the client.
--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com
Nov 13 '05 #2
Yeah that makes sense. The only user having privileges I spoke of is
good ole dbo and all of the linked tables start out with dbo_ prefix.

Thanks

Nov 13 '05 #3
Yeah that makes sense. The only user having privileges I spoke of is
good ole dbo and all of the linked tables start out with dbo_ prefix.

Thanks

Nov 13 '05 #4
But.... on the other hand there are a number of Crystal Reports that
run against this SQL database. They connect directly (not via Access
linked tables) and the connection string uses a userid/password that
Does Not have Select rights on the DB. The report has embedded SQL
and it runs fine.

How does that work?

Nov 13 '05 #5
ZRexRider wrote:
Yeah that makes sense. The only user having privileges I spoke of is
good ole dbo and all of the linked tables start out with dbo_ prefix.

Thanks


The prefix however doesn't say anything about the user who created the link.
Only who owns the object on the server.

--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com
Nov 13 '05 #6
Bri
ZRexRider,

Take a look at the Roles that the Userid has assigned to it. It could be
getting its rights through that rather than any explicitly granted to
the UserID. This is comparible to Accesses User/Group security in that
you can assign rights to a group and then put the User into the group.
Then, even though the User doesn't have any rights, they get the rights
assigned to the Group.

--
Bri
ZRexRider wrote:
Hi,

I am working on an Access application where the author used SQL back
end via linked tables.

I wrote some general functions that would execute pass through queries
and was going to call them for all new DB interaction.

They work fine as long as I go into SQL Server and give the User
Select, Update, Insert and Delete privileges. However this Access
application using internal queries is running fine with virtually every
table in the SQL database not offering ANY privileges to this user.

As this is a large organization I would rather not go thru the "red
tape" with the DBA staff to change numerous SQL DB settings to support
pass through so I will probably continue what the author started.

My question is... what is the difference? How does Access through
linked tables successfully Select, Update, Insert and Delete when the
user ID used to connect doesn't have any privilges according to SQL
Server?

Thanks


Nov 13 '05 #7
ZRexRider wrote:
My question is... what is the difference? How does Access through
linked tables successfully Select, Update, Insert and Delete when the
user ID used to connect doesn't have any privilges according to SQL
Server?


I work wih Oracle and mot MS SQL, but my first guess would be the ODBC
DSN used for the linked tables is not the one you think it is.

In Oracle, at least, linked tables inherit the privileges of the Oracle
database user.

Some folks, even myself, from time to time, get mixed up with the name
of the DSN and the server database user name.

SOme other fellows such as Rick Brandt would better be able to answer
this, but I saw your Q and figured I'd at least throw out the obvious so
you can thoroughly check that first. 8)
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "What's UP, Dittoooooo?" - Ditto
Nov 13 '05 #8
yeah that was basically it. This user was assigned to a role which had
all of the rights needed.

Thanks everybody for taking the time

Have a great weekend

Nov 13 '05 #9

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

6
by: Andreas Lauffer | last post by:
I changed from Access97 to AccessXP and I have immense performance problems. Details: - Access XP MDB with Jet 4.0 ( no ADP-Project ) - Linked Tables to SQL-Server 2000 over ODBC I used...
1
by: annie | last post by:
Hi all, I have recently ported my Access 2000 app to SQL Server, keeping the Access client as the front end using linked tables. I am also using triggers on my SQL tables to trap orphan...
8
by: Alfonso Esteban Gonzalez Sencion | last post by:
I am trying to use Access as a front end for extracting information from an Oracle database. I started using linked tables but I am getting a very curious behaviour. When I consult the linked...
7
by: Joe | last post by:
I am using Access 2003 and are linking to an Oracle 9i ODBC datasource (using Oracle ODBC drivers). After linking the tables in Access, I inspect the data contained in the linked tables. For...
15
by: brettclare | last post by:
I have linked a large SQL Server table to Access, however 'only' 2,195,439 records are shown and are available to query. Can I increase the size (cache??)/number of records showing in Access? ...
3
by: Zlatko Matić | last post by:
Hi! What happens with linked tables if they were linked using File DSN, when I copy the Access file on some other PC without File DSN ? What is the difference between DSN on linked tables and...
2
by: me321701 | last post by:
I have an Access 2003 MDE with a SQL Server 2000 back-end using ODBC linked tables. The MDE is shared among approximately 20 users. This is the environment; I don't have the ability to change it...
6
by: jsacrey | last post by:
Hey everybody, got a secnario for ya that I need a bit of help with. Access 97 using linked tables from an SQL Server 2000 machine. I've created a simple query using two tables joined by one...
3
by: Bret Kuhns | last post by:
I recently started a co-op/internship at a company and they are looking to migrate a large legacy supported application from OLEDB to SQL Server. I'm doing prelim work in experimenting with the...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.