Help connecting ole/db linked server to msaccess database in a different machine than sql server

Hi,

I have a msaccess linked server that I use to execute sql 2000 stored procedures from a front end in adp (access data project) format without any problem, if it is used on the same machine where the sql server resides (with any user logged on). In any other machine on the local network where I also need to use it, I get an ole/db error message saying that the microsoft jet database engine can not open the file because it's allready opened exclusivly or because it do not has permissions. I created the linked server with both UNC and normal path with the same result.

Thank's for all the help/clues you can give me.

Hi!

This is usually a permissions issue. The account that the SQL Server runs under needs to have NT permissions to the directory where the MDB is stored.

|||

Thank's for your help Cindy, but the case is that I can't figure out what to do to solve the problem.

I am sorry but I don't know exactly what do you mean by "the account that SQL Server runs".

The users that are running the sp that reports the error all have rights in the local network directory where the mdb is stored.

Could you please be kind enough to continue helping me on this?

|||Running SQL Server Service needs a service account to run with. The account can be determined using e.g. the Service Control manager. The access for file access is impersonated using the service account which is running SQL Server. If this has no access to the UNC share (e.g. System Account) you won′t be able to access the file / establish a link to the "server" / Access database. In addition to the explanation you should not use a mapped drive letter always use UNC paths working with SQL Server. it cannot be guranteed that the account which is running the process has also the mapped drived letter assigned to his profile.

Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||

The service account SQL Server is using is the network login account of the users.
All of them have access to the network drive that is referenced in the linked server by the UNC path.

This is the code I used to set the linked server:

USE master
GO
EXEC sp_addlinkedserver
@.server = 'CRED85', -- Name of the linked Server
@.provider = 'Microsoft.Jet.OLEDB.4.0', -- Access Provider
@.srvproduct = 'dbaccess', -- may be anything
@.datasrc = '\\Gcxncliflsv301h\Aplic\AplicDep\DAI\APLIC\Produtos\Cred85.mdb' -- UNC path + Access db name
GO

and after that:

exec sp_addlinkedsrvlogin 'dbaccess', false, 'GRUPOCGD\c008645', 'Admin', NULL

So, my question remains:
- Why does it work only when the front end is executed in the machine where resides the server (with any user)
and NOT in any other machine? What's wrong?

Help connecting ole/db linked server to msaccess database in a different machine than sql server

Hi,

I have a msaccess linked server that I use to execute sql 2000 stored procedures from a front end in adp (access data project) format without any problem, if it is used on the same machine where the sql server resides (with any user logged on). In any other machine on the local network where I also need to use it, I get an ole/db error message saying that the microsoft jet database engine can not open the file because it's allready opened exclusivly or because it do not has permissions. I created the linked server with both UNC and normal path with the same result.

Thank's for all the help/clues you can give me.

Hi!

This is usually a permissions issue. The account that the SQL Server runs under needs to have NT permissions to the directory where the MDB is stored.

|||

Thank's for your help Cindy, but the case is that I can't figure out what to do to solve the problem.

I am sorry but I don't know exactly what do you mean by "the account that SQL Server runs".

The users that are running the sp that reports the error all have rights in the local network directory where the mdb is stored.

Could you please be kind enough to continue helping me on this?

|||Running SQL Server Service needs a service account to run with. The account can be determined using e.g. the Service Control manager. The access for file access is impersonated using the service account which is running SQL Server. If this has no access to the UNC share (e.g. System Account) you won′t be able to access the file / establish a link to the "server" / Access database. In addition to the explanation you should not use a mapped drive letter always use UNC paths working with SQL Server. it cannot be guranteed that the account which is running the process has also the mapped drived letter assigned to his profile.

Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||

The service account SQL Server is using is the network login account of the users.
All of them have access to the network drive that is referenced in the linked server by the UNC path.

This is the code I used to set the linked server:

USE master
GO
EXEC sp_addlinkedserver
@.server = 'CRED85', -- Name of the linked Server
@.provider = 'Microsoft.Jet.OLEDB.4.0', -- Access Provider
@.srvproduct = 'dbaccess', -- may be anything
@.datasrc = '\\Gcxncliflsv301h\Aplic\AplicDep\DAI\APLIC\Produtos\Cred85.mdb' -- UNC path + Access db name
GO

and after that:

exec sp_addlinkedsrvlogin 'dbaccess', false, 'GRUPOCGD\c008645', 'Admin', NULL

So, my question remains:
- Why does it work only when the front end is executed in the machine where resides the server (with any user)
and NOT in any other machine? What's wrong?

Help - user permissions question

Hi,
I have a database user with read-only permissions in the database.
The user would like to be able to display a list of stored procedures
in the database. I would like to grant this user the permission to do
this, but without granting permission to *change* the sp's in any way.
Is there a way to do this?
THANKSRead only permission is enough to display the stored procedures list. Here is
the query:
/* It will list the names of all stored proc */
select name from sysobjects
where type = 'P'
order by name
/* It will list names of user stored procs only */
select name from sysobjects
where type = 'P' and status not like '-%'
order by name
Bob
"tootsuite@.gmail.com" wrote:
> Hi,
> I have a database user with read-only permissions in the database.
> The user would like to be able to display a list of stored procedures
> in the database. I would like to grant this user the permission to do
> this, but without granting permission to *change* the sp's in any way.
> Is there a way to do this?
> THANKS
>|||What version of SQL Server?
In 2000, you can see all objects in the database.
In 2005, you can see the objects that you have permissions to use (execute). You can grant
permission to see an object even if you cannot execute/select from etc it.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<tootsuite@.gmail.com> wrote in message news:1156963477.201608.121260@.74g2000cwt.googlegroups.com...
> Hi,
> I have a database user with read-only permissions in the database.
> The user would like to be able to display a list of stored procedures
> in the database. I would like to grant this user the permission to do
> this, but without granting permission to *change* the sp's in any way.
> Is there a way to do this?
> THANKS
>|||SQL 2000 or SQL 2005?
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
<tootsuite@.gmail.com> wrote in message
news:1156963477.201608.121260@.74g2000cwt.googlegroups.com...
> Hi,
> I have a database user with read-only permissions in the database.
> The user would like to be able to display a list of stored procedures
> in the database. I would like to grant this user the permission to do
> this, but without granting permission to *change* the sp's in any way.
> Is there a way to do this?
> THANKS
>|||Yes, sorry forgot to mention - SQL Server 2005
I know permissions work differently on 2005 than 2000. Please tell me I
do *not* have to grant execute permission to the user for each and
every user stored procedure in the database'
Is there another way to allow the user to see?
Arnie Rowland wrote:
> SQL 2000 or SQL 2005?
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> <tootsuite@.gmail.com> wrote in message
> news:1156963477.201608.121260@.74g2000cwt.googlegroups.com...
> > Hi,
> >
> > I have a database user with read-only permissions in the database.
> >
> > The user would like to be able to display a list of stored procedures
> > in the database. I would like to grant this user the permission to do
> > this, but without granting permission to *change* the sp's in any way.
> >
> > Is there a way to do this?
> >
> > THANKS
> >|||Yes, in SQL Server 2005 you can grant user "View Definition" permission so
that they can see the stored procedures but not alter or execute it.
For example:
GRANT VIEW DEFINITION ON OBJECT::DatabaseNAME.usp_StoredProc
TO User;
GO
"tootsuite@.gmail.com" wrote:
> Yes, sorry forgot to mention - SQL Server 2005
> I know permissions work differently on 2005 than 2000. Please tell me I
> do *not* have to grant execute permission to the user for each and
> every user stored procedure in the database'
> Is there another way to allow the user to see?
>
> Arnie Rowland wrote:
> > SQL 2000 or SQL 2005?
> >
> > --
> > Arnie Rowland, Ph.D.
> > Westwood Consulting, Inc
> >
> > Most good judgment comes from experience.
> > Most experience comes from bad judgment.
> > - Anonymous
> >
> >
> > <tootsuite@.gmail.com> wrote in message
> > news:1156963477.201608.121260@.74g2000cwt.googlegroups.com...
> > > Hi,
> > >
> > > I have a database user with read-only permissions in the database.
> > >
> > > The user would like to be able to display a list of stored procedures
> > > in the database. I would like to grant this user the permission to do
> > > this, but without granting permission to *change* the sp's in any way.
> > >
> > > Is there a way to do this?
> > >
> > > THANKS
> > >
>|||Hi,
Thanks - do I have to run this statement for each and every stored
procedure I wish to grant user permissions on? Or is there a single
stmt I can use?
Thanks
Bob wrote:
> Yes, in SQL Server 2005 you can grant user "View Definition" permission so
> that they can see the stored procedures but not alter or execute it.
> For example:
> GRANT VIEW DEFINITION ON OBJECT::DatabaseNAME.usp_StoredProc
> TO User;
> GO
>
> "tootsuite@.gmail.com" wrote:
> > Yes, sorry forgot to mention - SQL Server 2005
> >
> > I know permissions work differently on 2005 than 2000. Please tell me I
> > do *not* have to grant execute permission to the user for each and
> > every user stored procedure in the database'
> >
> > Is there another way to allow the user to see?
> >
> >
> >
> > Arnie Rowland wrote:
> > > SQL 2000 or SQL 2005?
> > >
> > > --
> > > Arnie Rowland, Ph.D.
> > > Westwood Consulting, Inc
> > >
> > > Most good judgment comes from experience.
> > > Most experience comes from bad judgment.
> > > - Anonymous
> > >
> > >
> > > <tootsuite@.gmail.com> wrote in message
> > > news:1156963477.201608.121260@.74g2000cwt.googlegroups.com...
> > > > Hi,
> > > >
> > > > I have a database user with read-only permissions in the database.
> > > >
> > > > The user would like to be able to display a list of stored procedures
> > > > in the database. I would like to grant this user the permission to do
> > > > this, but without granting permission to *change* the sp's in any way.
> > > >
> > > > Is there a way to do this?
> > > >
> > > > THANKS
> > > >
> >
> >|||You can create a list of stored procedures from INFORMATION_SCHEMA.ROUTINES
where ROUTINE_TYPE = 'PROCEDURE'.
You may wish to filter out procedures starting with 'dt'.
Then using dynamic SQL, cycle through the list making the appropriate
changes.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
<tootsuite@.gmail.com> wrote in message
news:1157052897.903180.182290@.i3g2000cwc.googlegroups.com...
> Hi,
> Thanks - do I have to run this statement for each and every stored
> procedure I wish to grant user permissions on? Or is there a single
> stmt I can use?
> Thanks
>
> Bob wrote:
>> Yes, in SQL Server 2005 you can grant user "View Definition" permission
>> so
>> that they can see the stored procedures but not alter or execute it.
>> For example:
>> GRANT VIEW DEFINITION ON OBJECT::DatabaseNAME.usp_StoredProc
>> TO User;
>> GO
>>
>> "tootsuite@.gmail.com" wrote:
>> > Yes, sorry forgot to mention - SQL Server 2005
>> >
>> > I know permissions work differently on 2005 than 2000. Please tell me I
>> > do *not* have to grant execute permission to the user for each and
>> > every user stored procedure in the database'
>> >
>> > Is there another way to allow the user to see?
>> >
>> >
>> >
>> > Arnie Rowland wrote:
>> > > SQL 2000 or SQL 2005?
>> > >
>> > > --
>> > > Arnie Rowland, Ph.D.
>> > > Westwood Consulting, Inc
>> > >
>> > > Most good judgment comes from experience.
>> > > Most experience comes from bad judgment.
>> > > - Anonymous
>> > >
>> > >
>> > > <tootsuite@.gmail.com> wrote in message
>> > > news:1156963477.201608.121260@.74g2000cwt.googlegroups.com...
>> > > > Hi,
>> > > >
>> > > > I have a database user with read-only permissions in the database.
>> > > >
>> > > > The user would like to be able to display a list of stored
>> > > > procedures
>> > > > in the database. I would like to grant this user the permission to
>> > > > do
>> > > > this, but without granting permission to *change* the sp's in any
>> > > > way.
>> > > >
>> > > > Is there a way to do this?
>> > > >
>> > > > THANKS
>> > > >
>> >
>> >
>|||There is no single statement, you have to run the grant statement for every
single stored proc. Or you can write a query which would print the grant
statements for you for every stored proc.
"tootsuite@.gmail.com" wrote:
> Hi,
> Thanks - do I have to run this statement for each and every stored
> procedure I wish to grant user permissions on? Or is there a single
> stmt I can use?
> Thanks
>
> Bob wrote:
> > Yes, in SQL Server 2005 you can grant user "View Definition" permission so
> > that they can see the stored procedures but not alter or execute it.
> >
> > For example:
> >
> > GRANT VIEW DEFINITION ON OBJECT::DatabaseNAME.usp_StoredProc
> > TO User;
> > GO
> >
> >
> >
> > "tootsuite@.gmail.com" wrote:
> >
> > > Yes, sorry forgot to mention - SQL Server 2005
> > >
> > > I know permissions work differently on 2005 than 2000. Please tell me I
> > > do *not* have to grant execute permission to the user for each and
> > > every user stored procedure in the database'
> > >
> > > Is there another way to allow the user to see?
> > >
> > >
> > >
> > > Arnie Rowland wrote:
> > > > SQL 2000 or SQL 2005?
> > > >
> > > > --
> > > > Arnie Rowland, Ph.D.
> > > > Westwood Consulting, Inc
> > > >
> > > > Most good judgment comes from experience.
> > > > Most experience comes from bad judgment.
> > > > - Anonymous
> > > >
> > > >
> > > > <tootsuite@.gmail.com> wrote in message
> > > > news:1156963477.201608.121260@.74g2000cwt.googlegroups.com...
> > > > > Hi,
> > > > >
> > > > > I have a database user with read-only permissions in the database.
> > > > >
> > > > > The user would like to be able to display a list of stored procedures
> > > > > in the database. I would like to grant this user the permission to do
> > > > > this, but without granting permission to *change* the sp's in any way.
> > > > >
> > > > > Is there a way to do this?
> > > > >
> > > > > THANKS
> > > > >
> > >
> > >
>|||Yes, that is what I thought - thanks
Arnie Rowland wrote:
> You can create a list of stored procedures from INFORMATION_SCHEMA.ROUTINES
> where ROUTINE_TYPE = 'PROCEDURE'.
> You may wish to filter out procedures starting with 'dt'.
> Then using dynamic SQL, cycle through the list making the appropriate
> changes.
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> <tootsuite@.gmail.com> wrote in message
> news:1157052897.903180.182290@.i3g2000cwc.googlegroups.com...
> > Hi,
> >
> > Thanks - do I have to run this statement for each and every stored
> > procedure I wish to grant user permissions on? Or is there a single
> > stmt I can use?
> >
> > Thanks
> >
> >
> > Bob wrote:
> >> Yes, in SQL Server 2005 you can grant user "View Definition" permission
> >> so
> >> that they can see the stored procedures but not alter or execute it.
> >>
> >> For example:
> >>
> >> GRANT VIEW DEFINITION ON OBJECT::DatabaseNAME.usp_StoredProc
> >> TO User;
> >> GO
> >>
> >>
> >>
> >> "tootsuite@.gmail.com" wrote:
> >>
> >> > Yes, sorry forgot to mention - SQL Server 2005
> >> >
> >> > I know permissions work differently on 2005 than 2000. Please tell me I
> >> > do *not* have to grant execute permission to the user for each and
> >> > every user stored procedure in the database'
> >> >
> >> > Is there another way to allow the user to see?
> >> >
> >> >
> >> >
> >> > Arnie Rowland wrote:
> >> > > SQL 2000 or SQL 2005?
> >> > >
> >> > > --
> >> > > Arnie Rowland, Ph.D.
> >> > > Westwood Consulting, Inc
> >> > >
> >> > > Most good judgment comes from experience.
> >> > > Most experience comes from bad judgment.
> >> > > - Anonymous
> >> > >
> >> > >
> >> > > <tootsuite@.gmail.com> wrote in message
> >> > > news:1156963477.201608.121260@.74g2000cwt.googlegroups.com...
> >> > > > Hi,
> >> > > >
> >> > > > I have a database user with read-only permissions in the database.
> >> > > >
> >> > > > The user would like to be able to display a list of stored
> >> > > > procedures
> >> > > > in the database. I would like to grant this user the permission to
> >> > > > do
> >> > > > this, but without granting permission to *change* the sp's in any
> >> > > > way.
> >> > > >
> >> > > > Is there a way to do this?
> >> > > >
> >> > > > THANKS
> >> > > >
> >> >
> >> >
> >