Re: Renamed Windows login not found in SQL Server 2000

From: mike singer (nospam_zookeeper@wwwhr.com)
Date: 12/31/02 

From: "mike singer" <nospam_zookeeper@wwwhr.com>
Date: Tue, 31 Dec 2002 13:27:16 -0500

Wow Lori, I was logging in to ask the exact same question. Also notice
that if you own a database. SQL will show domain\oldname as the owner and
any attempt to change the own w/ the sp will say new user not found.
Stopping the service does not help. When the system reboots, the owner now
shows correctly. It's almost like SQL tuck some knowledge away in an area
of memory that only gets released on Windows stop. It is wacky. Let's hope
that someone out there has an answer as we are about to switch to a new
naming convention and this will creat headaches. We are trying to do as
much as possible w/ groups so this is less of an issue, but a few user
rights have snuck in.

"Lori Landis" <lrlandis@hotmailnospam.com> wrote in message
news:05a201c2b028$5eea29a0$8df82ecf@TK2MSFTNGXA02...
> I am running SQL Server 2000 SP2 with Windows
> authentication. My problem is that whenever a rename of
> a Windows login is required (someone gets married, old
> login gets corrupted) I am unable to add the new login to
> SQL Server without a reboot of the server. Our admin
> group prefers to do a rename rather than a delete of the
> old Windows login and add of the new one because the
> rename retains all their old group memberships and other
> rights. Here is my process. Delete the old login from
> SQL Server security using Enterprise Manager. It deletes
> fine. There is no sign of the login in sysxlogins. Try
> to add the new login in Enterprise Manager. Here's the
> weird part. You can choose the new login from the domain
> list by clicking the button next to the Name field in the
> SQL Server Login Properties - New Login window, but when
> you try to save it, you get the message "Windows NT user
> or group xxx not found". Because I'm picking from the
> list, I know I'm not mispelling or otherwise fouling up
> the login name. You get the same results using
> sp_grantlogin. I tried stopping and restarting the SQL
> Server instance but it still refused to recognize the
> login. The only way I am able to get the server to
> recognize the renamed login is by rebooting it. Once I
> reboot, the new login adds fine, but this is impractical
> and not very timely since I must wait for a time window
> when I can reboot the server without impacting production
> work. According to this link
> http://msdn.microsoft.com/library/default.asp?
> url=/library/en-us/dnsql2k/html/sql_security2000.asp the
> rename of a Windows NT login is not supposed to affect
> SQL Server because it uses the SID, but this doesn't seem
> to be the case here since once the rename occurs, the
> user is unable to log in to SQL Server applications. We
> are using Active Directory and it appears to me that
> something is not being updated on our server from the
> domain, but since I am not involved with any our our AD
> administration, I don't have a lot of information in that
> area.
>
> Any suggestions would be very much appreciated.
>
> Thanks in advance,
>
> Lori Landis
>