Error 15401 using sp_grantlogin (not addressed by current KB articles)

From: Trevor Scroggins (trevor.scroggins@homeqabc.com)
Date: 10/01/02 

From: "Trevor Scroggins" <trevor.scroggins@homeqabc.com>
Date: Mon, 30 Sep 2002 22:01:25 -0700

Hello, all. . . .

As part of a corporate standardization project (you gotta love 'em), we've
renamed every account in our master NT4 domain. We have SQL Server 2000 SP1
installed on Windows 2000 Advanced Server SP2. The server is *not* installed
in case-senstive mode and the MSSQLServer service is running as a user in
the master domain that is in the local Administrator group on the server
itself. (FYI, the problem I'm describing occurs regardless of what account
is running the service--be it LocalSystem, Administrator, etc.) After an
account is renamed, SQL Server's integrated security no longer works. Now,
I'm not a DBA, so I'm going on what I've been able to see on the server,
with very little knowledge of how SQL Server 2000 works. Since I can't find
anything in the documentation related to renaming logins created with
sp_grantlogin, here's what I'm attempting to do:

exec sp_revokelogin 'DOMAIN\oldusername'
exec sp_grantlogin 'DOMAIN\newusername'

sp_grantlogin login fails with error 15401. However, the following works,
even though DOMAIN\oldusername no longer exists (it's been renamed to
DOMAIN\newusername):

exec cp_grantlogin 'DOMAIN\oldusername'

I've checked sysxlogin and every other visible table in every database and I
can find no reference to DOMAIN\oldusername. The sp_grantlogin request fails
in sp_MSaddlogin_implicit_ntlogin. In sp_MSaddlogin_implicit_ntlogin, the
call "get_sid('\U'+@loginname, NULL)" returns a NULL SID when passed
DOMAIN\newusername. It returns a valid SID when passed DOMAIN\oldusername.

What's going on? Why is it that SQL Server (and this mysterious get_sid
function) cannot see DOMAIN\newusername? Where is this phantom
DOMAIN\oldusername information being stored?

I can use sp_grantlogin with the old username whether it currently exists in
the domain or not, so if it's possible to rename a login, I'll go that
route. Am I going to have to go through the "moving a database to a server
in a different domain" garbage to get this done, even though I'm not moving
a database? This seems like a simple (and common) administrative task, so I
can't imagine it's not possible.

Any ideas?

Trevor Scroggins
trevor-dot-scroggins-at-homeq-dot-com

Relevant Pages

  • Re: Compromise?
    ... Yes, if you don't provide a password on your SA account, anybody able to run ... and connect now has complete control over your SQL Server. ... Server has. ...
    (microsoft.public.sqlserver.security)
  • Re: Windows Auth to SQL Server from ATL Web Service not working...
    ... account I'm logged on as. ... SQL on a different box from my web service in an Atl Server web ... impersonation token is not passed on to the SQL Server. ... Event Category: Account Logon ...
    (microsoft.public.vc.atl)
  • Re: Discussing 3 different strategies for deleting from multiple tables
    ... I will be using SQL Server but I am riding on top of a third party ... FYI, Account contains around 20K ... >>> This results in one parameterized query followed by two more trips to ...
    (microsoft.public.data.ado)
  • RE: connection problems in secondary site and SQL server
    ... Do you have a Windows 2003 server anywhere in your environment? ... i can't add this account to this group. ... SMS Management Point encountered an error when connecting to its Database ... SMS on SQL Server My_Primary_SMS_Server. ...
    (microsoft.public.sms.admin)
  • RE: MP Install issue
    ... Will setting the SPN on the domain account fix the communication issue ... >> MPDB ERROR - CONNECTION PARAMETERS ... >> with a trusted SQL Server connection. ...
    (microsoft.public.sms.setup)
Loading