AD group name edited, but SQL Server still shows old name

My databases in SQL Server are given access via an AD Group. The Net Admins changed the group name, but SQL Server still shows the old name.

How can I find out what the new AD Group name is? Also, how can I update so SQL Server reflects the new name?

Solution 1:

The reason is SQL Server just uses the initial name of the Active Directory group when the Group is stored in the SQL Server instance. In the background SQL Server actually stores the SID of the Windows Authenticated Active Directory group. Any changes to the Group's name on Active Directory level are not reflected in the SQL Server instance, because it is not really relevant for the authentication, as the SQL Server instance used the stored SID.

Register Windows AD Group as SQL Server Login

CREATE LOGIN [DOMAIN\OLD_NAME] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
GO

Query SQL Server Instance (AD) Groups

The registered Windows Groups that act as "SQL Server Logins" on a SQL Server instance can be queried with:

SELECT  ssp.name, 
        ssp.sid, 
        ssp.type, 
        ssp.type_desc  
FROM    sys.server_principals AS ssp 
WHERE   ssp.type = 'G'
GO

The statement will return something like this:

+-----------------+------------------------------------------------------------+------+---------------+
|      name       |                            sid                             | type |   type_desc   |
+-----------------+------------------------------------------------------------+------+---------------+
| DOMAIN\OLD_NAME | 0x010500000000000515000000EEEEEEEEEEEEEEEEEEEEEEEFFFFF0000 | G    | WINDOWS_GROUP |
+-----------------+------------------------------------------------------------+------+---------------+

Correcting SQL Server Login Name

Changing the name to the correct name can be achieved with the following statement:

ALTER LOGIN [DOMAIN\OLD_NAME] WITH NAME = [DOMAIN\NEW_NAME]
GO

Name : The new name of the login that is being renamed. If this is a Windows login, the SID of the Windows principal corresponding to the new name must match the SID associated with the login in SQL Server.

^{Reference: ALTER LOGIN (Transact-SQL) (Microsoft | SQL Docs)}