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

Question

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.

Answer 1

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

Answer 2

The query below will show you all of the users who have permissions on the server but do not have explicit permissions on the databases.

    SELECT P.name
     , D1.name AS DatabaseName_1
     , D2.name AS DatabaseName_2
    FROM sys.server_principals P
     LEFT OUTER JOIN <Database1>.sys.database_principals D1 ON D1.[SID] = P.[SID]
     LEFT OUTER JOIN <Database2>.sys.database_principals D2 ON D2.[SID] = P.[SID]
    WHERE D1.name IS NULL AND D2.name IS NULL

• Change "Database1" and "Database2" to be the relevant database names.