How to delete accounts from SQL server database that are no longer in Active Directory
We had a SQL database consultant who worked with us a few months ago and was called in again. Unfortunately, his AD account was deleted when his contract got over but was never removed from the SQL server. A new AD account created for him and when he tried to access the SQL database, he could not.
The user login on the SQL database was looking at the old AD account and gave the SID error, I was unable to delete the account as it held db_owner schema
To get around this issue, I did the following:
Step 1: I checked the 'Schema owned by this user' and database role membership' under [database name] - Security - Users - [user account] (Properties)
Step 2: I removed his AD account from all Roles that were assigned, under [database name] - Security - roles - database roles
Step 3: I removed his AD account from all Schemas that were assigned, under [database name] - Security - schemas
I was then able to delete his AD account from the database and add the new AD account.