its working in my side , please test before run it ; "PRINT @cmd"
for it USE [master]
GO
SET NOCOUNT ON
DECLARE @cmd varchar(4000)
BEGIN TRY
Create table #Orphan_User_Tbl
(
[Database_Name] sysname ,
[Orphaned_User] sysname
)
SET NOCOUNT ON
Create table #Windows_Auth_Orphan_User
(
[Str] nvarchar(300)
)
DECLARE @DBCount INT,@MaxCount INT, @Qry nvarchar(4000), @DBName sysname
DECLARE @db_list table (dbname nvarchar(100),ID int identity)
SET @Qry = ''
SET @DBCount = 1
INSERT INTO @db_list(dbname )
SELECT name FROM sys.sysdatabases
WHERE dbid > 4
SELECT @MaxCount = MAX(ID) FROM @db_list
WHILE(@DBCount < = @MaxCount )
BEGIN
SELECT @DBName = dbname FROM @db_list WHERE id = @DBCount
SET @Qry = 'SELECT ''' + @DBName + ''' AS [Database Name],
CAST(su.name AS sysname) AS [Orphaned User]
FROM ' + QUOTENAME(@DBName) + '..sysusers su
inner join master..syslogins b
on su.name=b.name
where
su.sid is not null
and su.sid not in (0x00,0x01)
and su.sid <> b.sid'
INSERT INTO #Orphan_User_Tbl EXEC (@Qry)
SET @DBCount = @DBCount + 1
END
DECLARE MC CURSOR
READ_ONLY
FOR
SELECT [Database_Name]+ '..sp_change_users_login ''UPDATE_ONE'' , ''' + Orphaned_User + ''' ,''' + Orphaned_User + ''';'
FROM #Orphan_User_Tbl
ORDER BY [Database_Name], [Orphaned_User]
OPEN MC
FETCH NEXT FROM MC INTO @cmd
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN TRY
PRINT @cmd
Execute (@cmd)
END TRY
BEGIN CATCH
SELECT ERROR_NUMBER()ErrorNumber,ERROR_LINE() AS ErrorLine,ERROR_MESSAGE() AS ErrorMessage
FETCH NEXT FROM MC INTO @cmd
CONTINUE;
END CATCH
FETCH NEXT FROM MC INTO @cmd
END
CLOSE MC
DEALLOCATE MC
---------------------------------------------------------------------------------------------------------
----Fixing Windows autheticated user-----------------
SET @DBCount = 1
WHILE(@DBCount < = @MaxCount )
BEGIN
SELECT @DBName = ''+ dbname + '' FROM @db_list WHERE id = @DBCount
SET @Qry = 'SELECT '' USE [' + @DBName + ' ];
ALTER USER ['' + NAME + ''] WITH LOGIN = [ '' + NAME + '']''
FROM msdb.sys.database_principals
WHERE ( type_desc = ''WINDOWS_GROUP'' OR type_desc = ''WINDOWS_USER'' )
AND name NOT like ''%dbo%'' AND name NOT LIKE ''%#%'''
INSERT INTO #Windows_Auth_Orphan_User EXEC (@Qry)
SET @DBCount = @DBCount + 1
END
--SELECT * FROM #Windows_Auth_Orphan_User
DECLARE MC CURSOR READ_ONLY FOR
SELECT [Str]FROM #Windows_Auth_Orphan_User
OPEN MC
FETCH NEXT FROM MC INTO @cmd
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN TRY
PRINT @cmd
Execute (@cmd)
END TRY
BEGIN CATCH
SELECT ERROR_NUMBER()ErrorNumber,ERROR_LINE() AS ErrorLine,ERROR_MESSAGE() AS ErrorMessage
FETCH NEXT FROM MC INTO @cmd
CONTINUE;
END CATCH
FETCH NEXT FROM MC INTO @cmd
END
CLOSE MC
DEALLOCATE MC
DROP Table #Orphan_User_Tbl
Drop table #Windows_Auth_Orphan_User
END TRY
BEGIN CATCH
SELECT ERROR_NUMBER()ErrorNumber,
ERROR_LINE() AS ErrorLine,
ERROR_MESSAGE() AS ErrorMessage
END CATCH
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)