Permission Questions After running DTS TransferLogins

I moved user DBs to a new SQL 2000sp4 server running win2003r2, also moved
the MSDB, not the Master, then I used DTS to transfer user logins to the new
SQL box. Now I have problems changing some login permissions. When I try
to add a database to a login I get this error > "error 15081: membership of
the public role cannot be changed" AND there I am unable to make any changes
to the public ROLE. Can some one tell me what I am missing ' Thanks you..Hi
Using the DTS package to transfer logins will not re-create the original
SIDs therefore you would need to resolve orphanded users!
sp_change_users_login 'report' would show any orphaned users, see
http://support.microsoft.com/kb/274188/ and
http://support.microsoft.com/kb/240872 for more.
John
"WANNABE" wrote:
> I moved user DBs to a new SQL 2000sp4 server running win2003r2, also moved
> the MSDB, not the Master, then I used DTS to transfer user logins to the new
> SQL box. Now I have problems changing some login permissions. When I try
> to add a database to a login I get this error > "error 15081: membership of
> the public role cannot be changed" AND there I am unable to make any changes
> to the public ROLE. Can some one tell me what I am missing ' Thanks you..
>
>

Permission Questions After running DTS TransferLogins

I moved user DBs to a new SQL 2000sp4 server running win2003r2, also moved
the MSDB, not the Master, then I used DTS to transfer user logins to the new
SQL box. Now I have problems changing some login permissions. When I try
to add a database to a login I get this error > "error 15081: membership of
the public role cannot be changed" AND there I am unable to make any changes
to the public ROLE. Can some one tell me what I am missing ' Thanks you..Hi
Using the DTS package to transfer logins will not re-create the original
SIDs therefore you would need to resolve orphanded users!
sp_change_users_login 'report' would show any orphaned users, see
http://support.microsoft.com/kb/274188/ and
http://support.microsoft.com/kb/240872 for more.
John
"WANNABE" wrote:

> I moved user DBs to a new SQL 2000sp4 server running win2003r2, also moved
> the MSDB, not the Master, then I used DTS to transfer user logins to the n
ew
> SQL box. Now I have problems changing some login permissions. When I try
> to add a database to a login I get this error > "error 15081: membership o
f
> the public role cannot be changed" AND there I am unable to make any chang
es
> to the public ROLE. Can some one tell me what I am missing ' Thanks you
.
>
>

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:
>
>