Find orphaned users in SQL Server 2008 after the upgrade from SQL Server 2005

• Mani-584606

  SSChampion

  Points: 11788

  More actions

  June 24, 2010 at 1:05 pm

  #236961

  Hi,

  I have upgraded the SQL Server 2005 instance to SQL Server 2008 and I'm trying to find the orphaned users in following way. But I would like to have a single script which will return all the orphaned users in each database.

  Could you please help me out. Thanks

  use Mydb1

  go

  sp_change_users_login 'report'

  use Mydb2

  go

  sp_change_users_login 'report'

  use Mydb3

  go

  sp_change_users_login 'report'

  use Mydb4

  go

  sp_change_users_login 'report'

  use Mydb5

  go

  sp_change_users_login 'report'

  use Mydb6

  go

  sp_change_users_login 'report'

  use Mydb7

  go

  sp_change_users_login 'report'

  use Mydb8

  go

  sp_change_users_login 'report'

  use Mydb9

  go

  sp_change_users_login 'report'

  use Mydb10

  go

  sp_change_users_login 'report'

  use Mydb11

  go

  sp_change_users_login 'report'

  use Mydb11

  go

  sp_change_users_login 'report'

• WayneS

  SSC Guru

  Points: 95460

  More actions

  June 24, 2010 at 1:08 pm

  #1184664

  if object_id('tempdb..#temp') IS NOT NULL DROP TABLE #temp

  CREATE TABLE #temp (DBName sysname, UserName sysname, UserSID varbinary(85))

  if object_id('tempdb..#temp2') IS NOT NULL DROP TABLE #temp2

  CREATE TABLE #temp2 (UserName sysname, UserSID varbinary(85))

  execute master..sp_msforeachdb

  'insert into #temp2

  execute [?]..sp_change_users_login @Action = ''Report'';

  insert into #temp select ''?'', UserName, UserSID from #temp2;

  delete from #temp2'

  select *,

  [Add User SQL] = 'USE ' + QuoteName(DBName) + ';IF EXISTS (select 1 FROM master.sys.server_principals WHERE name = ''' + UserName + ''') ' +

  'ALTER USER ' + QuoteName(UserName) + ' WITH LOGIN = ' + QuoteName(UserName)

  from #temp

  Wayne
  Microsoft Certified Master: SQL Server 2008
  Author - SQL Server T-SQL Recipes

  ---

  If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

  ---

  Links:
  For better assistance in answering your questions
  Performance Problems
  Common date/time routines
  Understanding and Using APPLY Part 1 & Part 2