Récuperer les utilisateurs orphelins

Après restaure la base de données MS SQL, il est possible que certains utilisateurs de la base de données soient orphelins. Alors, voici les SQL scripts pour les réparer.

Répare tous les utilisateurs orphelins de toutes bases de données:

DECLARE @sql as nvarchar(max);
SET @sql = '
USE[?];PRINT''Database: ''+db_name();DECLARE @username VARCHAR(25);DECLARE
GetOrphanUsers CURSOR FOR SELECT UserName=NAME FROM sysusers WHERE issqluser=1AND(sid IS
NOT NULL AND sid<>0x0)AND SUSER_SNAME(sid)IS NULL ORDER BY NAME;OPEN GetOrphanUsers;FETCH
NEXT FROM GetOrphanUsers INTO @username;WHILE @@FETCH_STATUS=0BEGIN IF @username=''dbo''
EXEC sp_changedbowner''sa'';ELSE BEGIN BEGIN TRY EXEC sp_change_users_login''update_one''
,@username,@username;END TRY BEGIN CATCH PRINT''Fail to fix database user: ''+@username;END CATCH
;END FETCH NEXT FROM GetOrphanUsers INTO @username;END;CLOSE GetOrphanUsers;DEALLOCATE
GetOrphanUsers;';
EXEC sp_MSforeachdb @sql

Stored Procedures :

CREATE PROCEDURE dbo.spDBA_FixOrphanUsers
AS
BEGIN
DECLARE @username VARCHAR(25)

DECLARE GetOrphanUsers CURSOR
FOR
SELECT UserName = NAME
FROM sysusers
WHERE issqluser = 1 AND (sid IS NOT NULL AND sid <> 0x0) AND SUSER_SNAME(sid) IS NULL
ORDER BY NAME

OPEN GetOrphanUsers

FETCH NEXT
FROM GetOrphanUsers
INTO @username

WHILE @@FETCH_STATUS = 0
BEGIN
IF @username = 'dbo'
EXEC sp_changedbowner 'sa'
ELSE
EXEC sp_change_users_login 'update_one', @username, @username

FETCH NEXT
FROM GetOrphanUsers
INTO @username
END

CLOSE GetOrphanUsers

DEALLOCATE GetOrphanUsers
END
GO

CREATE PROCEDURE dbo.spDBA_FixOrphanUsersPassWord
AS
BEGIN
 DECLARE @username VARCHAR(25)
 DECLARE @password VARCHAR(25)

 DECLARE GetOrphanUsers CURSOR
 FOR
 SELECT UserName = NAME
 FROM sysusers
 WHERE issqluser = 1 AND (sid IS NOT NULL AND sid <> 0x0) AND SUSER_SNAME(sid) IS NULL
 ORDER BY NAME

 OPEN GetOrphanUsers

 FETCH NEXT
 FROM GetOrphanUsers
 INTO @username

 SET @password = @username

 WHILE @@FETCH_STATUS = 0
 BEGIN
  IF @username = 'dbo'
   EXEC sp_changedbowner 'sa'
  ELSE
   EXEC sp_change_users_login 'Auto_Fix', @username, NULL, @password

  FETCH NEXT
  FROM GetOrphanUsers
  INTO @username
 END

 CLOSE GetOrphanUsers

 DEALLOCATE GetOrphanUsers
END
GO

CREATE PROCEDURE dbo.spDBA_DropOrphanUsers
AS
BEGIN
 DECLARE @username VARCHAR(25)

 DECLARE GetOrphanUsers CURSOR
 FOR
 SELECT UserName = NAME
 FROM sysusers
 WHERE issqluser = 1 AND (sid IS NOT NULL AND sid <> 0x0) AND SUSER_SNAME(sid) IS NULL
 ORDER BY NAME

 OPEN GetOrphanUsers

 FETCH NEXT
 FROM GetOrphanUsers
 INTO @username

 WHILE @@FETCH_STATUS = 0
 BEGIN
  IF @username = 'dbo'
   EXEC sp_changedbowner 'sa'
  ELSE
   EXEC sp_dropuser @username

  FETCH NEXT
  FROM GetOrphanUsers
  INTO @username
 END

 CLOSE GetOrphanUsers

 DEALLOCATE GetOrphanUsers
END

source: http://www.sqlservercentral.com/blogs/rocks/2011/10/20/finding-orphaned-database-users/

source: https://msdn.microsoft.com/en-us/library/ms175475.aspx