This is an interesting error that you’ll occasionally get when accessing an AD/Windows ID.
Msg 15404, Level 16, State 11, Line 6
Could not obtain information about Windows NT group/user ‘SQL2019TESTENVDopey’, error code 0x534.
Pretty simple reason here. The AD/Windows group/user no longer exists (or is inaccessible) but the entry for it exists inside of SQL.
Now what do I mean by “accessing”? Well the easiest way to get the error is to try to impersonate the id.
EXECUTE AS LOGIN = 'SQL2019TESTENVDopey'; EXECUTE AS USER = 'SQL2019TESTENVDopey';
I’ve most frequently seen this happen when someone has left the company (or a service account is removed for whatever reason) and the corresponding SQL principals (logins & users) have not been removed.
When this can get really interesting when you go to look up the name in AD/Windows and it’s still there!?! Basically what’s happened is that the SID has changed at the AD/Windows level. I believe this can happen when removing/re-adding an id but when I tested in Windows dropping and re-creating the Id gave me the same SID. I could be missing something though. Regardless the SID in SQL no longer has a match in AD/Windows.
Now that we have the error what do we use to fix it? Well, if the Id is gone and is supposed to be gone drop your associated logins and users. If on the other hand the Id still exists but the SID doesn’t match anymore it’s a bit more complicated.
First script the login (server principal) and all of it’s server level role memberships and permissions then drop and re-create it. Generally I use my sp_SrvPermissions stored procedure for this. You do not have drop any users (database principals). You will have to do the following in each of the databases were a related user exists.
ALTER USER Dopey WITH LOGIN = 'Dopey'
This will change the SID of the user to match the login.
To the best of my knowledge this can also happen with Azure SQL databases and AAD although I haven’t tested it yet.