Resynchronisation des users (sp_change_users_login) - SQL Server 2000

1- 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.

2- 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
• 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).

3- Diagnostic des désynchronisations

3-1- 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 :

sp_change_users_login @Action='Report'

3-1-1- Désynchronisation dans la base Northwind

> use Northwind
> go
> 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
> sp_helpuser 'Northwind_user1'
> go
UserName        GroupName      LoginName DefDBName UserID SID
--------        ---------      --------- --------- ------ --------------
Northwind_user1 Northwind_role NULL      NULL      7      0x88B3455D93465943B0EC028023BC015C

3-1-2- Désynchronisation dans la base pubs

> use pubs
> go
> 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.

4- 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.

4-1- 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 :

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

Exemple :

use pubs
go
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.

4-1-1- 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
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, toutefois le login pubs_user2. Avec la commande Auto_Fix, le login user2 est automatiquement créé sans mot de passe :

use pubs
go
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.

4-1-2- 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
sp_change_users_login @Action='Update_One',
@UserNamePattern='user2',
@LoginName='pubs_user2'
go

4-2- 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
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

sp_change_users_login @Action='Auto_Fix',
                     @UserNamePattern='Northwind_reader'
go
sp_change_users_login @Action='Auto_Fix',
                     @UserNamePattern='Northwind_writer'
go
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 Northwind_user2 qui doit être recréé sur le serveur CGC_T2_MSQ.

use Northwind
go
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.

5- Procédures automatiquesde 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.

5-1- 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

5-2-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

5-3- 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

5-4- 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

Annexe