Sql-server – removing sql logins from one server, after migrating to another server, but all the databases were not migra

loginsSecuritysql server

Here are the details of the production environment:

I have 2 serevrs, A1 and A2.

I have completely transfered 100 logins from server A1 to A2.

Server A1 contains 5 DATABASES. But I moved only 2 databases from A1 to A2 during the migration of databases from server A1 to Server A2.

I want to drop the users, who are not required on server A2, because of remaining databases which were not migrated on it.

Please help with any scripts or dmvs to identify which users are not required on server A2 and thereby dropping them.

If a user has access to multiple databases, how can we remove the login of that user whose concerned database was not migrated from server A1 to A2.

Thanks in advance, any valuable suggestions would be helpful for our POC.

Best Answer

Instead of migrating or transferring all the users from server A to server B, you should have identified the orphan users for the migrated databases.

Say from server 1 , as you said you moved database A and B:

1) Find orphan users on database A- Once you have found them , mapped the orphan users with help of script you used earlier to find the logins on server A

2) Find orphan users on database B- Once you have found them , mapped the orphan users with help of script you used earlier to find the logins on server A.

For more on how to find orphan and map them read this article on Orphan users

Related Question