SQL Server referencing user accounts that no longer exist

[neonetman]

When trying to copy a database from one Sql Server to another, I'm getting a 12550 error in the event log which reads:-

 

* InnerException-->Windows NT user or group '{removed for security}' not found. Check the name again.

 

The username relates to a user who has long since been deleted. I've looked in SSMS under both Security/Logins as well as under {database name}/security/users and can't see any refernece to this user.

 

I've tried re-adding this user in AD, which seems to resolve it but it then fails on a different deleted user (I know there may be a few "ghost" accounts for former technicians involved in this issue.

 

Obviously I need to find and remove the references to these former technicians accounts, but I have no idea where to look now.

 

Can anyone offer any insights as it's battering my tiny mind

[Geoff]

MS has documentation for this issue.

 

https://docs.microsoft.com/en-us/sql/sql-server/failover-clusters/troubleshoot-orphaned-users-sql-server

[neonetman]

Yeah - I looked at that, and the query

SELECT name, sid, principal_id
FROM sys.database_principals 
WHERE type = 'S' 
 AND name NOT IN ('guest', 'INFORMATION_SCHEMA', 'sys')
 AND authentication_type_desc = 'INSTANCE';

gave an error sayign

 

Msg 207, Level 16, State 1, Line 5
Invalid column name 'authentication_type_desc'.

 

I'm at my wits end with this now. I've even (apparently) got a local account on one server which means transferring that database causes an error on the new server.

 

I'm not sure quite how this has been set up by my precessors, but it's a bit of a nightmare

[Geoff]

I'm guessing this is an older SQL server? My 2k8r2 server doesn't have that column either. 2016 works though. Edited October 5, 2017 by Geoff

[neonetman]

It is Sql 2k8 as you suggest, hence the trying to migrate DB's to a new server

[Geoff]

Ok here's the 'old' docs. You have to use the stored procedures instead.

 

https://msdn.microsoft.com/en-us/library/ms175475(v=sql.110).aspx