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.
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 :
Instance CGC_T1_MSQ | Instance CGC_T2_MSQ | ||
---|---|---|---|
Login (base de données par déf.) Sid | Rôle (database) Nom en Base | Login (base de données par déf.) Sid | Rôle (database) Nom en Base |
Northwind_dbo (Northwind) 0xE6940D5E39A94E4F8533BC55968C492E | dbowner Northwind_dbo | Northwind_dbo (Northwind) 0x3D6289E5AEDB7A46A3D0385CFAD59957 | dbowner Northwind_dbo |
Northwind_reader (Northwind) 0x33CFB95F2391BF46B1A9842D0AE6A458 | db_datareader northwind_reader | Northwind_reader (Northwind) 0x95A255613027CB499E28E33CABAAFC9A | db_datareader northwind_reader |
Northwind_user1 (Northwind) 0x88B3455D93465943B0EC028023BC015C | Northwind_role Northwind_user1 | Northwind_user1 (Northwind) 0x0E07DB739970F441BD824D98C632B2B2 | / Northwind_user1 |
Northwind_user2 (Northwind) 0x5A1FAC8F0F2B514B956A7F9B5D2CFC73 | / user2 | / / | / / |
Northwind_writer (Northwind) 0x585AE7E89C44244E882F7C6AA40654BE | db_datawriter northwind_writer | Northwind_writer (Northwind) 0xC5C35D89BBE6FE47969536A8B0B3B944 | db_datawriter northwind_writer |
pubs_dbo (pubs) 0xCA92C84CBFD92A4887B87AA47A49E0D0 | dbowner pubs_dbo | pubs_dbo (pubs) 0x2E2225C98DF33A49BEE81D255A34E496 | dbowner pubs_dbo |
pubs_reader (pubs) 0xB5FF396C7862BE47ADC04FC807D3209D | db_datareader pubs_reader | pubs_reader (pubs) 0x23DE9F5E2F266F48930BF7B7AE31C9AB | db_datareader pubs_reader |
pubs_user1 (pubs) 0xFED6CDD7872711469B3D13E746356A89 | pubs_role pubs_user1 | pubs_user1 (pubs) 0xD23BEEA30AB67F4ABB790426EC193FE8 | pubs_role pubs_user1 |
pubs_user2 (pubs) 0x1A60B3053B39BB449D12E88FC7AF8E76 | / user2 | / / | / / |
pubs_writer (pubs) 0x40A72DC3C0E56D46A482B2859EBCD5FC | db_datawriter pubs_writer | pubs_writer (pubs) 0x904030F97740394687D2594F724D33EF | db_datawriter pubs_writer |
Parmi les points à noter :
Avec cette configuration concernant les logins et users :
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).
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'
> 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
> 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.
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 :
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'
Quelle que soit l'option (Auto_Fix ou Update_One), la resynchronisation ne peut être réalisée pour le dbo d'une base et le message n°15287 est affiché. Pour le dbo, la procédure stockée sp_changedbowner doit être utilisée.
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.
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.
Lorsque le login du même nom que le user n'existe pas, le login est automatiquement créé avec l'option Auto_Fix. Ce comportement est particulièrement dangereux et une attention toute particulière doit y être apportée. Il est préférable de ne pas lancer l'option 'Auto_Fix' pour un user n'ayant pas de login correspondant du même nom.
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.
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
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.
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 :
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.
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
La procédure stockée sp__dba_resync_users resynchronise des users avec des logins dans une base de données utilisateurs.
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
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
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
Version | Date | Commentaires |
---|---|---|
1.0 | 10/2006 | Version initiale |
MSDN Books Online, SQL Server 2000, Transact-SQL reference, sp_change_users_login