Resynchronisation des users (sp_change_users_login) - SQL Server 2000

Introduction

Le chargement d’une sauvegarde d’une base de données d’une instance SQL Server 2000 vers une autre instance SQL Server 2000 peut générer des désynchronisations au niveau des utilisateurs de bases de données entre les tables sysxlogins (base master) et sysusers (base chargée).

Cet article se propose se propose de montrer comment diagnostiquer les désynchronisations de users avec SQL Server 2000 et réparer ce problème.

Contexte de la désynchronisation

Dans le contexte de cet article, nous disposons de 2 instances SQL Server 2000 CGC_T1_MSQ et CGC_T2_MSQ pour lesquels les logins n’ont pas été créés dans le même ordonnancement pour les bases de données pubs et Northwind :

Parmi les points à noter :

• les logins Northwind_user2 et pubs_user2 n’ont pas été définis dans le serveur de destination CGC_T2_MSQ
• le rôle Northwind_role n’a pas été créé et attribué au user Northwind_user1 dans le serveur de destination CGC_T2_MSQ.

Avec cette configuration concernant les logins et users :

• une sauvegarde de la base Northwind sur le serveur CGC_T1_MSQ est restaurée sur le serveur CGC_T2_MSQ.
• une sauvegarde de la base pubs sur le serveur CGC_T1_MSQ est restaurée sur le serveur CGC_T2_MSQ.

restore database pubs
from disk='C:\sdata\mssql\MSSQL$CGC_T1_MSQ\BACKUP\pubs\pubs_db_200410221604.dmp'
go

Processed 208 pages for database 'pubs', file 'pubs' on file 1.
Processed 1 pages for database 'pubs', file 'pubs_log' on file 1.
RESTORE DATABASE successfully processed 209 pages in 0.393 seconds (4.340 MB/sec).

restore database Northwind
from disk='C:\sdata\mssql\MSSQL$CGC_T1_MSQ\BACKUP\Northwind\Northwind_db_200410221604.dmp'
go

Processed 384 pages for database 'Northwind', file 'Northwind' on file 1.
Processed 1 pages for database 'Northwind', file 'Northwind_log' on file 1.
RESTORE DATABASE successfully processed 385 pages in 0.750 seconds (4.197 MB/sec).

Diagnostic des désynchronisations

Procédure stockée sp_change_users_login

La procédure stockée sp_change_users_login de la base master permet de diagnostiquer rapidement les désynchronisations de users dans une base de données :

Syntaxe :

exec sp_change_users_login @Action='Report'

Désynchronisation dans la base Northwind

use Northwind
go
exec sp_change_users_login @Action='Report'
go

UserName             UserSID
-------------------- ------------------------------------------------------
dbo                  0xE6940D5E39A94E4F8533BC55968C492E
Northwind_reader     0x33CFB95F2391BF46B1A9842D0AE6A458
Northwind_user1      0x88B3455D93465943B0EC028023BC015C
Northwind_writer     0x585AE7E89C44244E882F7C6AA40654BE
user2                0x5A1FAC8F0F2B514B956A7F9B5D2CFC73

La procécudre stockée sp_change_users_login montre clairement que ce sont les SID du serveur CGC_T1_MSQ qui ont été remontés dans la table sysusers de la base Northwind, provoquant ainsi des désynchronisations.

Pour ce qui concerne le user Northwind_user1 dans la base Northwind, cet utilisateur a bien le rôle Northwind_role alors que ce dernier rôle n’existait pas dans la base Northwind avant rechargement, ainsi les rôles définis dans les bases de données sont bien réattribués utilisateurs lors d’un rechargement :

use Northwind
go
exec sp_helpuser 'Northwind_user1'
go

UserName        GroupName      LoginName DefDBName UserID SID
--------        ---------      --------- --------- ------ --------------
Northwind_user1 Northwind_role NULL      NULL      7      0x88B3455D93465943B0EC028023BC015C

Désynchronisation dans la base pubs

use pubs
go
exec sp_change_users_login @Action='Report'
go

UserName      UserSID
------------- ----------------------------------------
dbo           0xCA92C84CBFD92A4887B87AA47A49E0D0
pubs_reader   0xB5FF396C7862BE47ADC04FC807D3209D
pubs_user1    0xFED6CDD7872711469B3D13E746356A89
pubs_writer   0x40A72DC3C0E56D46A482B2859EBCD5FC
user2         0x1A60B3053B39BB449D12E88FC7AF8E76

La procécudre stockée sp_change_users_login montre clairement que ce sont les SID du serveur CGC_T1_MSQ qui ont été remontés dans la table sysusers de la base pubs.

Réparation de la désynchronisation

La procédure stockée sp_change_users_login va permettre de resynchroniser efficacement les logins et les users dans les bases de données Northwind et pubs.

Trois cas de figure vont se présenter :

• le user est le dbo de la base de données
• les logins et users ont le même nom
• le login n’existe pas pour un user dans la base de données.

Syntaxes de réparation avec sp_change_users_login

Deux syntaxes sont à notre disposition avec la procédure stockée sp_change_users_login pour resynchroniser les logins avec les users :

Syntaxes :

exec sp_change_users_login @Action='Auto_Fix', @UserNamePattern='user'

exec sp_change_users_login @Action='Update_One', @UserNamePattern='user', @LoginName='login'

Exemple :

use pubs
go
exec sp_change_users_login @Action='Update_One',
@UserNamePattern='dbo',
@LoginName='pubs_dbo'
go
Server: Msg 15287, Level 16, State 1, Procedure sp_change_users_login, Line 38
Terminating this procedure. 'dbo' is a forbidden value for the login name parameter in this procedure.

Option Auto_Fix

L’option Auto_Fix est généralement utilisée lorsque le nom du login est le même que le nom du user dans la base de données. C’est par exemple le cas du user pubs_reader à resynchroniser avec le login pubs_reader sur le serveur CGC_T2_MSQ :

use pubs
go
exec sp_change_users_login @Action='Auto_Fix',
@UserNamePattern='pubs_reader'
go
The row for user 'pubs_reader' will be fixed by updating its login link to a login already in existence.
The number of orphaned users fixed by updating users was 1.
The number of orphaned users fixed by adding new logins and then updating users was 0.

Le message retourné par la procédure sp_change_users_login indique que la table sysusers a été mise à jour pour le user pubs_reader afin de resynchroniser ce dernier user avec le login pubs_reader.

C’est le cas par exemple dans la base pubs du user user2 qui est normalement relié au login pubs_user2. Avec la commande Auto_Fix, le login user2 est automatiquement créé sans mot de passe :

use pubs
go
exec sp_change_users_login @Action='Auto_Fix',
@UserNamePattern='user2'
go
New login created.
Barring a conflict, the row for user 'user2' will be fixed by updating its link to a new login. Consider changing the
new password from null.
The number of orphaned users fixed by updating users was 0.
The number of orphaned users fixed by adding new logins and then updating users was 1.

Option Update_One

L’option Update_One est utilisée lorsque le nom du login n’est pas le même que le nom du user dans la base de données. C’est le cas par exemple du user user2 dans la base pubs qui doit être relié au login pubs_user2, or ce dernier login n’existe pas sur CGC_T2_MSQ.

Après création du login pubs_user2, pour synchroniser le user2 avec le login pubs_user2 :

use pubs
go
exec sp_change_users_login @Action='Update_One',
@UserNamePattern='user2',
@LoginName='pubs_user2'
go

Exemple de réparation de la désynchronisation de la base Northwind

Première étape : réattribution du dbo de la base Northwind

use Northwind
go
exec sp_changedbowner 'Northwind_dbo'
go

Seconde étape : resynchronisation des users disposant d’un login ayant le même nom avec l’option Auto_Fix de la procédure stockée sp_change_users_login

exec sp_change_users_login @Action='Auto_Fix',
                     @UserNamePattern='Northwind_reader'
go
exec sp_change_users_login @Action='Auto_Fix',
                     @UserNamePattern='Northwind_writer'
go
exec sp_change_users_login @Action='Auto_Fix',
                     @UserNamePattern='Northwind_user1'
go

Troisième étape : resynchronisation des users ne disposant d’un login ayant le même nom avec l’option Update_One de la procédure stockée sp_change_users_login.

Il est nécessaire de remapper le user user2 dans la base Northwind avec le login orthwind_user2 qui doit être recréé sur le serveur CGC_T2_MSQ.

use Northwind
go
exec sp_change_users_login @Action='Update_One',
@UserNamePattern='user2',
@LoginName='Northwind_user2'
go

Dernière étape : Vérification que tous les users sont resynchronisés avec l’option 'Report' de la procédure stockée sp_change_users_login.

Procédures automatiques de synchronisation

Des procédures automatiques peuvent être facilement développées afin d’alléger le travail de resynchronisation des users avec les logins, en voici deux exemples :

• sp__dba_get_orphanedusers
• sp__dba_resync_users

Ces procédures peuvent être créées dans une base quelconque (msdb par exemple). sp__dba_get_orphanedusers doit être créé avant sp__dba_resync_users.

sp__dba_get_orphanedusers

La procédure stockée sp__dba_get_orphanedusers permet d’obtenir un état rapide sur les users désynchronisés dans une base de données :

Syntaxe :

exec sp__dba_get_orphanedusers '<db_name>'

Exemples :

exec sp__dba_get_orphanedusers 'master'
go

Getting orphaned users for database [master]...
No Orphaned users in the database [master]

exec sp__dba_get_orphanedusers 'Northwind'

Getting orphaned users for database [Northwind]...
Orphaned users are detected in the database [Northwind]
name
----------------------------------------------------------------------------------
dbo
Northwind_reader
Northwind_user1
Northwind_writer
user2

exec sp__dba_get_orphanedusers 'pubs66'

Database name pubs66 is not a valid user database

sp__dba_resync_users

La procédure stockée sp__dba_resync_users resynchronise des users avec des logins dans une base de données utilisateurs.

• La resynchronisation dans les bases de données système master, msdb, model et tempdb n’est pas autorisée.
• Un user d’une base de données est automatiquement mappé à un login du même nom, en d’autres termes la procédure stockée sp_change_users_login n’est appelée avec l’option Auto_Fix que si un login du même nom que le user existe.
• Des avertissements sont donnés à l’issue de l’exécution pour des users ne disposant pas de logins du même nom ainsi que pour le dbo qui n’a pu être remappé à un login.

Syntaxe :

exec sp__dba_resync_users '<db_name>'

Exemples :

exec sp__dba_resync_users 'tempdb'

Database name tempdb is a system database, this stored procedure can't be
used for these ones

exec sp__dba_resync_users 'pubs66'

Database name pubs66 is not a valid user database

exec sp__dba_resync_users 'Northwind'

    Getting orphaned users for database [Northwind]...
    Orphaned users are detected in the database [Northwind]
    5 users should be resync. in database [Northwind]
    Starting Resync.
    Trying to resync user [dbo] in database [Northwind]...
    --> Resync is not allowed for the database owner, instead execute the stored procedure sp_changedbowner
    Trying to resync user [Northwind_reader] in database [Northwind]...
    The row for user 'Northwind_reader' will be fixed by updating its login link to a login already in existence.
    The number of orphaned users fixed by updating users was 1.
    The number of orphaned users fixed by adding new logins and then updating users was 0.
    Trying to resync user [Northwind_user1] in database [Northwind]...
    The row for user 'Northwind_user1' will be fixed by updating its login link to a login already in existence.
    The number of orphaned users fixed by updating users was 1.
    The number of orphaned users fixed by adding new logins and then updating users was 0.
    Trying to resync user [Northwind_writer] in database [Northwind]...
    The row for user 'Northwind_writer' will be fixed by updating its login link to a login already in existence.
    The number of orphaned users fixed by updating users was 1.
    The number of orphaned users fixed by adding new logins and then updating users was 0.
    Trying to resync user [user2] in database [Northwind]...
    --> User [user2] can not be mapped with a login with the same name, resync will not be done
    End Resync.
    Verifying Resync.
    -------------------------------------------------------------------------------
    dbo : KO, Use sp_changedbowner to resync.
    user2 : KO, Could not be mapped to an existing login

Code source sp__get_orphanedusers

IF exists (SELECT 1 FROM sysobjects where type='P' and name = 'sp__dba_get_orphanedusers')
BEGIN
    drop procedure sp__dba_get_orphanedusers
END
go

create procedure sp__dba_get_orphanedusers @dbname varchar(30)=NULL,
@action varchar(10) = 'Report'
AS

    /**
    * Get Orphaned Users
    *
    * Usage : sp__dba_get_ophanedusers @dbname (user database),@action (Report | Resync)
    * Date : 10.2004
    * Author : Stephane PAQUOT
    *
    * Version : 1.0
    */
    
SET NOCOUNT ON

DECLARE @cmd varchar(256),
@countOrphanedUsers integer

IF @action is NULL
BEGIN
  SELECT @action='Report'
END

/**
* Checking parameter @action
*/

IF @action NOT IN ('Report','Resync')
BEGIN
  PRINT 'The parameter action is invalid : Report or Resync'
  RETURN -1
END


/**
* Checking parameter @dbname is supplied
*/
IF @dbname is null
BEGIN
  PRINT 'Parameter Database Name @dbname is not supplied'
  RETURN -1
END


/**
* Checking database name is valid
*/
IF NOT EXISTS (SELECT 1 FROM master..sysdatabases where name=@dbname)
BEGIN
  PRINT 'Database name '+@dbname +' is not a valid user database'
  RETURN -1
END


/**
* Checking this stored procedure is not invoked against master, model or msdb for resync option
*/
IF @action='Resync'
BEGIN
  IF @dbname in ('master','model','msdb','tempdb')
  BEGIN
    PRINT 'Database name '+@dbname +' is a system database, this stored procedure can''t be used for these ones'
    RETURN -1
  END
END


/**
* Getting not valid users in a database
*/
PRINT 'Getting orphaned users for database ['+ @dbname +']...'

BEGIN
SET ANSI_WARNINGS OFF

IF @action='Report'
BEGIN
  CREATE TABLE #_sys_OrphanedUsers (
    name sysname,
    sid varbinary
  )
END

SELECT @cmd = 'SELECT name, sid FROM [' + @dbname + ']..sysusers where issqluser = 1 and (sid is not null and sid <> 0x0) and suser_sname(sid) is null order by name'

INSERT INTO #_sys_OrphanedUsers
EXEC (@cmd)

SET ANSI_WARNINGS ON

SELECT @countOrphanedUsers = count(1) FROM #_sys_OrphanedUsers

IF @countOrphanedUsers > 0
BEGIN
  PRINT 'Orphaned users are detected in the database ['+ @dbname +']'
  IF @action='Report'
  BEGIN
    SELECT name FROM #_sys_OrphanedUsers
    DROP TABLE #_sys_OrphanedUsers
  END
  ELSE
  BEGIN
    RETURN @countOrphanedUsers
  END
END

ELSE
BEGIN
  PRINT 'No Orphaned users in the database ['+ @dbname +']'
  RETURN 0
END

END

Code source sp__dba_resync_users

IF exists (SELECT 1 FROM sysobjects where type='P' and name = 'sp__dba_resync_users')
BEGIN
    drop procedure sp__dba_resync_users
END
go
create procedure sp__dba_resync_users @dbname varchar(30)=NULL
AS
   /**
   * Resync users on user databases after loading
   * - no changes are allowed for system databases
   * - no changes are made for the dbo
   * - no changes are made for users not mapped with a login with the same name
   *
   * Usage : sp__dba_resync_users @dbname (user database)
   * Date : 10.2004
   * Author : Stephane PAQUOT
   *
   * Version : 1.0
   */
   
SET NOCOUNT ON

DECLARE @cmd varchar(256),
@uname sysname,
@usid varbinary,
@retval integer,
@retstring varchar(20)

CREATE TABLE #_sys_OrphanedUsers (
  name sysname,
  sid varbinary
)

CREATE TABLE #tmpResults (
  name sysname,
  reason varchar(2),
  action varchar(60)
)

EXEC @retval = sp__dba_get_orphanedusers @dbname,'Resync'

IF @retval > 0
BEGIN
  SELECT @retstring = convert(varchar(20),@retval)
  PRINT @retstring + ' users should be resync. in database ['+ @dbname + ']'
  PRINT ''
  PRINT 'Starting Resync.'
  PRINT ''
  
  DECLARE c_orphanedUsers cursor global for
  SELECT sid,name FROM #_sys_OrphanedUsers
  OPEN c_orphanedUsers
  FETCH NEXT FROM c_orphanedUsers INTO @usid,@uname
  WHILE (@@FETCH_STATUS <> -1)
  BEGIN
    PRINT 'Trying to resync user ['+ @uname+'] in database [' + @dbname + ']...'
   /** Checking dbo */
   IF @uname = 'dbo'
     BEGIN
       INSERT INTO #tmpResults VALUES ('dbo','KO','Use sp_changedbowner to resync.')
       PRINT '--> Resync is not allowed for the database owner, instead execute the stored procedure sp_changedbowner'
     END
   ELSE
     BEGIN
     IF NOT EXISTS (SELECT 1 FROM master..syslogins WHERE name=@uname)
       BEGIN
         INSERT INTO #tmpResults VALUES (@uname,'KO','Could not be mapped to an existing login')
         PRINT '--> User ['+ @uname +'] can not be mapped with a login with the same name, resync will not be done'
       END
     ELSE
       BEGIN
         SELECT @cmd = 'use [' + @dbname + '] exec sp_change_users_login @Action=''Auto_Fix'', @UserNamePattern='''+ @uname +''''
         EXEC (@cmd)
       END
     END
   
   FETCH NEXT FROM c_orphanedUsers INTO @usid,@uname
   
   END

   CLOSE c_orphanedUsers
   DEALLOCATE c_orphanedUsers

   PRINT ''
   PRINT 'End Resync.'
   PRINT ''
   PRINT ''
   PRINT 'Verifying Resync.'
   PRINT ''
   SELECT name + ' : ' + reason + ', ' + action from #tmpResults
END

DROP TABLE #_sys_OrphanedUsers
DROP TABLE #tmpResults

go