Remove user which is not existing

• IT researcher

  SSCertifiable

  Points: 7616

  More actions

  April 5, 2013 at 2:16 am

  #291777

  In Sql server 2008 r2 i created a login test and gave some user roles for some databases by using SSMS.Now i have deleted the login "test". But the test login still appear under databases -security-users for those databses for which i gave user roles. It won't get delete from there. Now i want that user to get delete under those databases. How can i do it by using sql query? the query should find under which database that user is there and delete it for all the database in server.

• AJ@SQL

  SSC Eights!

  Points: 811

  More actions

  April 5, 2013 at 3:01 am

  #1603832

  Did you check this?

  http://www.mssqltips.com/sqlservertip/2624/script-to-find-and-drop-all-orphaned-users-in-all-sql-server-databases/

• John Mitchell-245523

  SSC Guru

  Points: 148809

  More actions

  April 5, 2013 at 3:06 am

  #1603833

  IF USER_ID('test') IS NOT NULL

  DROP USER test

  Run against all databases. If you have so many that that would be tedious, use sp_ms_foreachdb.

  For future reference, you can do all that automatically in the User Mappings page in the GUI when you delete the login.

  John