[SQL Server] Corriger les comptes
USE master
GO
SET ANSI_NULLS ON
GO SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE usp_autofix_users
AS
-- Mode d'utilisation:
-- pour chaque BD: SP_MSFOREACHDB "USE ? GO; EXEC sp_autofix_users;"
-- pour une BD: USE laBD GO; EXEC sp_autofix_users;
-- Déclaration de variables
SET NOCOUNT ON
DECLARE @login SYSNAME
PRINT DB_NAME() PRINT '–-–-–-–-'
DECLARE user_update_cursor CURSOR FAST_FORWARD
FOR
SELECT DISTINCT name FROM sysusers
WHERE issqluser = 1 AND name NOT IN('dbo', 'guest', 'sys', 'INFORMATION_SCHEMA')
ORDER BY name
OPEN user_update_cursor
-- Lancement des opérations
FETCH NEXT
FROM user_update_cursor INTO @login
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @login
EXEC sp_change_users_login'update_one', @login, @login
-- Tant qu'il n'y a pas d'erreurs bloquantes...
FETCH NEXT FROM user_update_cursor INTO @login
END
CLOSE user_update_cursor
DEALLOCATE user_update_cursor