Modifying Sysxlogins Domain Names in SQL2000, strange result..

[Steven Durfey]

It seens that a simple script:

Update sysxlogins set name = 'AA001' + substring(name, 9, LEN(name)-8
)
where name like 'ILLINOIS%'

Will replace the SQL2000 domain name correctly in sysxlogins:
ILLINOIS\JonesP becomes AA001\JonesP

But for some strange reason via ILLINOIS\JonesP can still logon via
QueryAnalyzer although he is no longer in the sysxlogins table
anymore? SQL has been stop/started, server even rebooted, yet BOTH
the new and old logins seem to both allow QA login, any thought how
the old one is getting thru SQL security?

thanks in advance for any help...

[Erland Sommarskog]

Re: Modifying Sysxlogins Domain Names in SQL2000, strange result..

Steven Durfey (s_durfey@hotma il.com) writes:[color=blue]
> It seens that a simple script:
>
> Update sysxlogins set name = 'AA001' + substring(name, 9, LEN(name)-8
> )
> where name like 'ILLINOIS%'
>
> Will replace the SQL2000 domain name correctly in sysxlogins:
> ILLINOIS\JonesP becomes AA001\JonesP
>
> But for some strange reason via ILLINOIS\JonesP can still logon via
> QueryAnalyzer although he is no longer in the sysxlogins table
> anymore? SQL has been stop/started, server even rebooted, yet BOTH
> the new and old logins seem to both allow QA login, any thought how
> the old one is getting thru SQL security?[/color]

Updates to the system table is nothing that is particularly supported,
or at all. Not that I know what you are trying to achieve, but this
might not be the method to do it. But I would suspect that the column
sid has something to do with it. But it may be even more complex than
this.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[mnocj@yahoo.com]

Re: Modifying Sysxlogins Domain Names in SQL2000, strange result..

The sid is the login. Test this by running sp_grantlogin for the same
domain account on different servers. I found this out because I wanted
to update the sid of a QA login on a QA server in sysxlogins to match
the sid of the production application service account. This would let
me refresh QA and not have to do any login mapping or permissions
updates. However, if the sid does not match the login, the login does
not seem to work.