Sql-server – Need to list SQL Windows auth users on each SQL server and rename the domain name

loginssql server

We are doing an AD Domain NetBIOS rename. We have about 20 SQL servers, and on each we have a series of logins like OLD.DOM\sqluser1 etc. I want to do two things, and a third would be a bonus. I'm not sure how to put these together:

SELECT * FROM Sys.login_token

I think this will list the users I mentioned above? I'd like to put that into a variable and then run this I think:

ALTER LOGIN "OLD.DOM\variable_holding_username"
WITH NAME="NEWDOM\variable_holding_username"

So if I can go on a server, list out all of those OLD.DOM\ logins and ALTER each of them, I'd be happy.

I'd be extra happy if I could hit each SQL server remotely and run through this, instead of doing each one separately.

Best Answer

Unless you are actually deleting the user accounts in Active Directory and re-creating them, you don't actually need to do anything to the logins on SQL Server.

SQL Server references the accounts using the Active Directory SID, not the name.

You can test this by renaming an account in Active Directory and attempting to login to SQL Server with it.

Assuming you are using at least a semi-modern version1 of SQL Server, you can use ALTER LOGIN [DOMAIN\xyz] WITH NAME = [NEWDOMAIN\xyz]; syntax to modify the name of a Windows Active Directory login in SQL Server. You can use the system DMV, sys.server_principals to determine which logins should be modified. For example:

DECLARE @OldDomain SYSNAME;
DECLARE @NewDomain SYSNAME;
DECLARE @cmd NVARCHAR(MAX);

SET @OldDomain = 'MyOldDomain';
SET @NewDomain = 'NewDomainName';

DECLARE cur CURSOR LOCAL FORWARD_ONLY
FOR
SELECT 'ALTER LOGIN ' + QUOTENAME(sp.name) + 
    ' WITH NAME = ' + QUOTENAME(@NewDomain + 
    RIGHT(sp.name, CHARINDEX('\', REVERSE(sp.name)))) + ';'
FROM master.sys.server_principals sp
WHERE sp.type_desc LIKE 'WINDOWS_%'
    AND sp.name LIKE @OldDomain + '\%';

OPEN cur;

FETCH NEXT FROM cur 
INTO @cmd;

WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT (@cmd);

    FETCH NEXT FROM cur 
    INTO @cmd;
END

CLOSE cur;
DEALLOCATE cur;

The above code will print a list of commands that could be executed to modify the logins for Windows User Accounts and Windows Groups.

You can run the above code against multiple servers by creating a group of servers in the Server Registration Window in SSMS, the right-clicking it and selecting "New Query", as shown here:

enter image description here

This allows you to run any query against multiple servers simultaneously, and see the results in one window. The results have a "Server Name" column added automatically so you determine where each row came from.

1 - SQL Server 2008+

Related Question