im getting creasy here with linked servers in sql 2000

i would like to create a linked server with stored procedure
sp_addlinkedserver
(excel table 8.0)

sqlCommand="sp_addlinkedserver N'Excel', N'Jet 4.0',
N'Microsoft.Jet.OLEDB.4.0', " & "N'" & strExportFile & "', NULL, N'Excel
5.0'"

its working, if i add the user which is opening the databse (ASP scripting)
to the sys admin group. otherwise i get the error message

"user has not permission to perform this action"

this is the first problem and question: which rights has to be set for a
public user so he can add linked servers? i saw that linked servers are in
table sysservers in master DB
but giving access to public user to this table (writing and deleting
permissions) sound not very secure to me...

the second problem
if i add the public user to sys admin and then execute the query for
inserting records into the excel table from SQL table per INSERT INTO
Excel...calls, im getting the error:

the information file for the workgroup is missing or is opened exklusiv by
another user
(translated from german)

here is the code:

sqlCommand="sp_addlinkedserver N'Excel', N'Jet 4.0',
N'Microsoft.Jet.OLEDB.4.0', " & "N'" & strExportFile & "', NULL, N'Excel
5.0'"
set rs=conn.execute (cstr(sqlCommand))

sqlCommand="sp_addlinkedsrvlogin N'Excel', false, sa, NULL, NULL"
set rs=conn.execute (cstr(sqlCommand))

sqlExport = "INSERT INTO EXCEL...calls$
(datum,zeit,zielort,zielnummer
,dauer,betrag,rufnummer) " & sql
conn.Execute(sqlExport)

sqlCommand="sp_dropserver N'Excel', N'droplogins'"
set rs=conn.execute (cstr(sqlCommand))

does anybody find solutions to my problems??

would be great
have a nice day/night
mike

Report this post to a moderator

See Linked Servers/ Security in BOL. Also:

exec sp_addlinkedsrvlogin

exec sp_droplinkedsrvlogin

exec sp_dropserver

exec sp_helplinkedsrvlogin

EXEC sp_addlinkedserver

"Mike Schwarz" <ctek@ctek.ch> wrote in message
news:ujdapkJnCHA.2344@TK2MSFTNGP11...
> im getting creasy here with linked servers in sql 2000
>
> i would like to create a linked server with stored procedure
> sp_addlinkedserver
> (excel table 8.0)
>
> sqlCommand="sp_addlinkedserver N'Excel', N'Jet 4.0',
> N'Microsoft.Jet.OLEDB.4.0', " & "N'" & strExportFile & "', NULL, N'Excel
> 5.0'"
>
> its working, if i add the user which is opening the databse (ASP
scripting)
> to the sys admin group. otherwise i get the error message
>
> "user has not permission to perform this action"
>
> this is the first problem and question: which rights has to be set for a
> public user so he can add linked servers? i saw that linked servers are in
> table sysservers in master DB
> but giving access to public user to this table (writing and deleting
> permissions) sound not very secure to me...
>
> the second problem
> if i add the public user to sys admin and then execute the query for
> inserting records into the excel table from SQL table per INSERT INTO
> Excel...calls, im getting the error:
>
> the information file for the workgroup is missing or is opened exklusiv by
> another user
> (translated from german)
>
> here is the code:
>
> sqlCommand="sp_addlinkedserver N'Excel', N'Jet 4.0',
> N'Microsoft.Jet.OLEDB.4.0', " & "N'" & strExportFile & "', NULL, N'Excel
> 5.0'"
> set rs=conn.execute (cstr(sqlCommand))
>
> sqlCommand="sp_addlinkedsrvlogin N'Excel', false, sa, NULL, NULL"
> set rs=conn.execute (cstr(sqlCommand))
>
> sqlExport = "INSERT INTO EXCEL...calls$
> (datum,zeit,zielort,zielnummer
,dauer,betrag,rufnummer) " & sql
> conn.Execute(sqlExport)
>
> sqlCommand="sp_dropserver N'Excel', N'droplogins'"
> set rs=conn.execute (cstr(sqlCommand))
>
> does anybody find solutions to my problems??
>
> would be great
> have a nice day/night
> mike
>
>

Report this post to a moderator

thank you
everbody is talking here of BOL (books online)
is it downloadable anywhere from microsoft ?


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Report this post to a moderator

Its not on your SQL? Strange. Anyways, Im sure its downloadable but I dont
have the link. Go to www.microsoft.com/sql and Im sure you'll find it
somewhere.

"mike schwarz" <ctek@ctek.ch> wrote in message
news:Or0D67JnCHA.1608@TK2MSFTNGP08...
>
> thank you
> everbody is talking here of BOL (books online)
> is it downloadable anywhere from microsoft ?
>
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

Report this post to a moderator