Lorsque l’on restaure des bases de données sur un autre serveur que celui d’origine, le lien est coupé entre compte de connexion et utilisateurs SQL. La recréation des comptes de connexion, ne suffit pas. Il faut recoller les SID. C’est ce que propose cette procédure qui génère un script de recollement.

CREATE PROCEDURE dbo.P_REMATCH_USER_LOGIN 

AS 

 

DECLARE @T TABLE  

(DBNAME    sysname, 

 DONE      BIT); 

 

DECLARE @TT TABLE 

(SQL_USER   sysname, 

 SYS_LOGIN  sysname)  

 

DECLARE @TSQL TABLE  

(ID           INT IDENTITY, 

 COMMANDE_SQL VARCHAR(max))  

 

INSERT INTO @T  

SELECT name, 0  

FROM   sys.databases  

WHERE  name NOT IN ('master', 'msdb', 'tempdb', 'model',  

                    'distribution', 'ReportServer', 'ReportServerTempDB'); 

                     

DECLARE @SQL VARCHAR(max), @DBNAME sysname; 

 

WHILE EXISTS (SELECT * 

              FROM   @T 

              WHERE  DONE = 0) 

BEGIN 

   SELECT @DBNAME = DBNAME  

   FROM   @T 

   WHERE  DONE = 0; 

    

   DELETE FROM @TT; 

    

   SET @SQL = 'SELECT dp.name AS SQL_USER, sp.name AS SYS_LOGIN ' +  

              'FROM   ' + @DBNAME + '.sys.database_principals AS dp ' + 

              'INNER JOIN sys.server_principals AS sp ' + 

              'ON dp.sid = sp.sid;'; 

   INSERT INTO @TT 

   EXEC (@SQL); 

    

   INSERT INTO @TSQL VALUES ('USE ' + @DBNAME + ';'); 

    

   INSERT INTO @TSQL 

   SELECT 'EXEC sp_change_users_login ''auto_fix'', ''' + SQL_USER + ''', ''' + SYS_LOGIN +''';' 

   FROM   @TT; 

    

   UPDATE @T 

   SET    DONE = 1 

   WHERE  DBNAME = @DBNAME; 

    

END 

 

SELECT COMMANDE_SQL  

FROM @TSQL 

ORDER BY ID; 

 

GO

-------- 

Frédéric Brouard, SQLpro - ARCHITECTE DE DONNÉES, http://sqlpro.developpez.com/ 

Expert bases de données relationnelles et langage SQL. MVP Microsoft SQL Server 

www.sqlspot.com : modélisation, conseil, audit, optimisation, tuning, formation 

* * * * *  Enseignant CNAM PACA - ISEN Toulon - CESI Aix en Provence  * * * * *