The SQL Server login catalogs store the names of Windows principals as well as their SIDs. Because the names are stored, changes that affect a name can lead to a state where a catalog entry is out of sync with the current login name. For example, in the case of local Windows accounts, if we change the machine name, the catalogs will reflect the old machine name. Also, if we change the name of an account, the catalog will still contain the old account name. One side-effect of this will be that queries that rely on logic like select * from syslogins where name = suser_name() will be broken. What needs to be done here is to update the login names using the ALTER LOGIN statement. Based on the LookupSid function that I introduced in my previous post, I wrote a small T-SQL script that loops over the server_principals catalog entries and verifies whether the names recorded are matching the names that Windows returns for the associated SIDs. If differences are detected, the script will output the ALTER LOGIN statements that will fix the situation. Note that no data is changed here; the script is simply producing as output the statements that should be run to rectify the problem. Here's the script:
---- Execute this to generate the list of statements that need to be executed-- to fix the Windows login names that are invalid--declare @login_name sysnamedeclare @login_sid varbinary(max)declare crs_fix_login_names cursor for select name, sid from sys.server_principals where lower(name) <> lower(dbo.LookupSid(sid)) AND (type = 'U' or TYPE = 'G')
open crs_fix_login_namesfetch next from crs_fix_login_names into @login_name, @login_sidwhile (@@fetch_status = 0)begin print 'ALTER LOGIN ' + quotename(@login_name) + ' WITH NAME = ' + quotename(dbo.LookupSid(@login_sid)) fetch next from crs_fix_login_names into @login_name, @login_sidendclose crs_fix_login_namesdeallocate crs_fix_login_namesgo
So, the script is using a cursor to go over each Windows login entry (of type U or G - Windows user or group) and verify whether the name in the catalog is different from the name returned by the OS for the SID. For each such instance, the script builds the ALTER LOGIN command that needs to be executed to update the login name. Nothing is actually modified by this code. If there is any output, it should be copied and examined for errors, then executed to correct the login names. If there is no output, then no problems were detected by the script.