Permission error on transfer to protected Excel worksheet

I am new to the forum and hope this issue is in the right place.
I have a SQL query that is transferring data from a SQL table to an Excel
spreadsheet on the same machine. I keep getting the following error unless I
unprotect the worksheet. I don't want to expose the unprotected worksheet to
users.
Server: Msg 7344, Level 16, State 1, Line 342
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' could not INSERT INTO table
'[Microsoft.Jet.OLEDB.4.0]' because of column 'MyColumnA'. The user did not
have permission to write to the column.
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0'
IRowsetChange::InsertRow returned 0x80040e21: Data status sent to the
provider: [COLUMN_NAME=MyColumnA STATUS=DBSTATUS_S_OK],
[COLUMN_NAME=MyColumnB STATUS=DBSTATUS_S_OK], [COLUMN_NAME=MyColumnC
STATUS=DBSTATUS_S_OK], [COLUMN_NAME=MyColumnD STATUS=DBSTATUS_S_OK],
[COLUMN_NAME=MyColumnE STATUS=DBSTATUS_S_OK], ...
The SQL statement I am running under the sa login is as follows. The
spreadsheet has a column header row on row 3 and a couple rows of sample dat
a
that I wish to lock and protect. If I unprotect the sheet, the query runs
without error and inserts the data correctly into the spreadsheet. Any help
will be appreciated.
INSERT INTO
OPENROWSET('Microsoft.Jet.OLEDB.4.0'
,'Excel 8.0;Database=C:\myfile.xls;'
,'select * from [Import Template$A3:S7]'
)
(
[MyColumnA],
[MyColumnB],
[MyColumnC],
[MyColumnD],
[MyColumnE],
[MyColumnF],
[MyColumnG]
)
SELECT
#MyTempTable.MyColumnA,
#MyTempTable.MyColumnB,
#MyTempTable.MyColumnC,
#MyTempTable.MyColumnD,
#MyTempTable.MyColumnE,
#MyTempTable.MyColumnF,
#MyTempTable.MyColumnG
FROM
#MyTempTable
Sincerely,
GeraldAre you executing the query from Excel or from SQL Server? If not, you
might want to try that. A better solution than Excel protection would
be to limit access to the share on the local machine using Windows
ACLs. This way access is restricted via the file system if users can
connect to that machine through the network. Grant the account that
you are using for SQL Server permissions on that share. The security
offered by all Office applications, such as Access and Excel, is very
weak and has been bypassed long ago by attackers, so it's pretty much
a waste of time if the data truly needs to be secured.
--Mary
On Wed, 2 Nov 2005 13:17:18 -0800, "Gerald Hopkins"
<GeraldHopkins@.discussions.microsoft.com> wrote:

>I am new to the forum and hope this issue is in the right place.
>I have a SQL query that is transferring data from a SQL table to an Excel
>spreadsheet on the same machine. I keep getting the following error unless
I
>unprotect the worksheet. I don't want to expose the unprotected worksheet t
o
>users.
>Server: Msg 7344, Level 16, State 1, Line 342
>OLE DB provider 'Microsoft.Jet.OLEDB.4.0' could not INSERT INTO table
>'[Microsoft.Jet.OLEDB.4.0]' because of column 'MyColumnA'. The user did not
>have permission to write to the column.
>OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0'
>IRowsetChange::InsertRow returned 0x80040e21: Data status sent to the
>provider: [COLUMN_NAME=MyColumnA STATUS=DBSTATUS_S_OK],
>[COLUMN_NAME=MyColumnB STATUS=DBSTATUS_S_OK], [COLUMN_NAME=MyColumnC
>STATUS=DBSTATUS_S_OK], [COLUMN_NAME=MyColumnD STATUS=DBSTATUS_S_OK],
>[COLUMN_NAME=MyColumnE STATUS=DBSTATUS_S_OK], ...
>
>The SQL statement I am running under the sa login is as follows. The
>spreadsheet has a column header row on row 3 and a couple rows of sample da
ta
>that I wish to lock and protect. If I unprotect the sheet, the query runs
>without error and inserts the data correctly into the spreadsheet. Any help
>will be appreciated.
>INSERT INTO
>OPENROWSET('Microsoft.Jet.OLEDB.4.0'
>,'Excel 8.0;Database=C:\myfile.xls;'
>,'select * from [Import Template$A3:S7]'
> )
> (
> [MyColumnA],
> [MyColumnB],
> [MyColumnC],
> [MyColumnD],
> [MyColumnE],
> [MyColumnF],
> [MyColumnG]
> )
>SELECT
> #MyTempTable.MyColumnA,
> #MyTempTable.MyColumnB,
> #MyTempTable.MyColumnC,
> #MyTempTable.MyColumnD,
> #MyTempTable.MyColumnE,
> #MyTempTable.MyColumnF,
> #MyTempTable.MyColumnG
>FROM
> #MyTempTable|||Mary,
Thanks for your response. I am executing the query from Query Analyzer. The
spreadsheet can be filled in manually OR automatically via the export query.
The data is not critical. I just have the header, some hidden rows and a
couple sample data rows that I want to protect. The protection is mainly for
when the users fill the spreadsheet manually. Would Windows ACL actually let
me protect several rows at the top of a spreadsheet and let the user insert,
edit and delete rows under them?
Sincerely,
Gerald
"Mary Chipman [MSFT]" wrote:

> Are you executing the query from Excel or from SQL Server? If not, you
> might want to try that. A better solution than Excel protection would
> be to limit access to the share on the local machine using Windows
> ACLs. This way access is restricted via the file system if users can
> connect to that machine through the network. Grant the account that
> you are using for SQL Server permissions on that share. The security
> offered by all Office applications, such as Access and Excel, is very
> weak and has been bypassed long ago by attackers, so it's pretty much
> a waste of time if the data truly needs to be secured.
> --Mary
> On Wed, 2 Nov 2005 13:17:18 -0800, "Gerald Hopkins"
> <GeraldHopkins@.discussions.microsoft.com> wrote:
>
>|||No, it's file-based, not application based. I'd tackle the problem
from the Excel side, not QA.
--Mary
On Sat, 5 Nov 2005 10:52:01 -0800, "Gerald Hopkins"
<GeraldHopkins@.discussions.microsoft.com> wrote:

>Mary,
>Thanks for your response. I am executing the query from Query Analyzer. The
>spreadsheet can be filled in manually OR automatically via the export query
.
>The data is not critical. I just have the header, some hidden rows and a
>couple sample data rows that I want to protect. The protection is mainly fo
r
>when the users fill the spreadsheet manually. Would Windows ACL actually le
t
>me protect several rows at the top of a spreadsheet and let the user insert
,
>edit and delete rows under them?|||OK. Thanks for the advice.
--
Sincerely,
Gerald
"Mary Chipman [MSFT]" wrote:

> No, it's file-based, not application based. I'd tackle the problem
> from the Excel side, not QA.
> --Mary
> On Sat, 5 Nov 2005 10:52:01 -0800, "Gerald Hopkins"
> <GeraldHopkins@.discussions.microsoft.com> wrote:
>
>