Migrating database with many orphan users.

• JasonO

  SSC-Addicted

  Points: 488

  More actions

  December 3, 2024 at 8:01 am

  #4492509

  I am currently upgrading a very old database running SQL Server 2008 to SQL Server 2022 using export & import method to a new server.

  I have migrated the necessary objects e.g. logins, linked servers & configuration to the new server successfully. For logins, I use revlogin procedure to script out all the logins from the old server & execute it on the new server. However there's quite a number of windows logins weren't created as they don't exist in our AD anymore. This behaviour is expected since nobody has perform any housekeeping to old server.

  I then proceed to take the full backup of the old DB & restore it in SQL 2022, restoration works fine and DB is running on SQL 2022 with it's compatibility adjusted accordingly. Since I restore the full backups, all the DB users are restored together this includes hundreds of the orphan windows logins which no longer exist.

  I can manually delete the orphan window logins but this will take time for me to comb through users line by line & with multiple database to be migrated, I'm hoping if there's any faster method to do this.

• Site Owners

  SSC Guru

  Points: 80342

  More actions

  December 4, 2024 at 8:06 am

  #4493048

  Thanks for posting your issue and hopefully someone will answer soon.

  This is an automated bump to increase visibility of your question.

• frederico_fonseca

  SSCoach

  Points: 16234

  More actions

  December 4, 2024 at 10:09 am

  #4493095

  have a look at https://www.sqlshack.com/how-to-discover-and-handle-orphaned-database-users-in-sql-server/