Correct invalid SID

I migrated my SQL 2000 secuity from one NT Domain to another. In the process of changing the login names in the master..sysxlogins table, the SID did not get updated.

Is there an easy way to correct the SID entry without dropping and recreating each user?

Do you mean that you have manually changed the login names in sysxlogins and you wish to update the SID entries as well? Can you explain what you meant by "the process of changing the login names in the master..sysxlogins table"?

Thanks
Laurentiu

|||

I ran the following in the master db:

UPDATE sysxlogins

Set [name] = 'NEWDOMAIN\' + substring([name], patindex('%\%', [name])+1, 200)

WHERE [name] like 'OLDDOMAIN\%'

It changed all the login names to point to the new domain. The problem was the SIDs changed (which I didn't think about) in the new domain. Users can get in, but when we try to use the function suser_sid(), the correct network sid is being returned and we cant compare it to the one in sysxlogins or sysusers because it doesn't match.

|||

If you move from domain A to domain B, the Windows logins from domain A are normally invalidated. An exception to this would be if domain B was trusted by domain A, then you could still use the A logins even though the server runs in domain B.

This kind of domain change is not a supported operation. There is no supported solution for fixing this. It's not only the logins that you would need to fix, but all the database users as well. If you plan to change the domain for your server often, then you should use only SQL authentication.

Thanks
Laurentiu

sql

Correct access to TempDB?

uff... I've another problem...
In this loop I've same ADO 2.7 error (number: -2147217865,
description: Invalid object name '#tabella_temp') at STEP 3:

'--STEP 1--
sSql = "IF OBJECT_ID('tempdb..#tabella') IS NOT NULL DROP TABLE
#tabella"
m_cn.Execute sSql, RowAff, adExecuteNoRecords

'--STEP 2--
sSql = "SELECT top 0 * INTO #tabella_temp FROM tabella"
m_cn.Execute sSql, RowAff, adExecuteNoRecords

'STEP --READ DATA--
sSql = "SELECT IdRow from TabellaIn"
rs.Open sSql, m_cn, adOpenForwardOnly, adLockReadOnly

Do While Not rs.EOF
'--STEP 3--
sSql = "insert into #tabella_temp (row) values (" & rs("IdRow") &
")"
m_cn.Execute sSql, RowAff, adExecuteNoRecords
rs.MoveNext
Loop
rs.CLose

why , why, why?zMatteo (origma@.edpsistem.it) writes:
> uff... I've another problem...
> In this loop I've same ADO 2.7 error (number: -2147217865,
> description: Invalid object name '#tabella_temp') at STEP 3:
> '--STEP 1--
> sSql = "IF OBJECT_ID('tempdb..#tabella') IS NOT NULL DROP TABLE
> #tabella"
> m_cn.Execute sSql, RowAff, adExecuteNoRecords
> '--STEP 2--
> sSql = "SELECT top 0 * INTO #tabella_temp FROM tabella"
> m_cn.Execute sSql, RowAff, adExecuteNoRecords
> 'STEP --READ DATA--
> sSql = "SELECT IdRow from TabellaIn"
> rs.Open sSql, m_cn, adOpenForwardOnly, adLockReadOnly
> Do While Not rs.EOF
> '--STEP 3--
> sSql = "insert into #tabella_temp (row) values (" & rs("IdRow") &
> ")"
> m_cn.Execute sSql, RowAff, adExecuteNoRecords
> rs.MoveNext
> Loop
> rs.CLose
>
> why , why, why?

Seems to be the same problem again. Your connection is busy with getting
data from TabellaIn, so ADO opens second connection for you, and then
the temp table is not there.

Two ways to address this:

o Use a client-side cursor. (Connection.CursorLocation = adUseClient)
o Explicitly use two connection, ond for data in and one for
data out.

(Actually I am not entirely sure that using a client-side cursor is
enough. But it's a good thing anyway.)

And of course, if all you do is copy data, it is much more effective
to do it down in SQL Server and not get the data forth and back over
the network.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp