SQL Server : resynchroniser un login avec le user d’une base après une restauration grâce au SID

Voici un problème qui est bien connu des DBAs SQL Server en matière d’attributions de droits et mappings.

Celui- ci est lié à la resynchronisation des logins et users bases de données après une restauration de bases.

Au sein d’une instance SQL Server, chaque login a un SID qui est un identifiant représentant une valeure hexadéciamale unique au sein du moteur SQL Server.

Il en va de même pour un user déclaré sur une base de données, ce SID est également unique et, il est généralement associé au login au moment du mapping du login au user base de données attaché.
Pour notre exemple, nous utiliserons une base de données nommé “capdata_db” et un user nommé “manu” qui sera dbo sur cette base. Le login “manu” existe déjà au sein de l’instance.

 

USE [capdata_db]
GO
CREATE USER [manu] FOR LOGIN [manu]
GO
USE [capdata_db]
GO
ALTER USER [manu] WITH DEFAULT_SCHEMA=[dbo]
GO

 

S’il l’on cherche les SID pour ce login et ce user bases de données, créé à partir de ce login, nous voyons que les SID sont identiques :

 

use master
select name, sid from sys.syslogins where name =’manu’
go

use capdata_db
select name, sid from sys.sysusers where name=’manu’
go

 

Le mapping pourra se voir dans les propriétés du login “manu” de l’instance.

 

 

Cas d’une restauration

On imagine que l’on est sur un serveur de recette, et que, dans le cadre de tests d’un nouvel applicatif, nous souhaitons tester les données fraîches de l’instance de production. Nous devrons alors, restaurer la base de données “capdata_db” à partir d’un serveur de production vers notre environnement de recette.

Nous utiliserons le dernier backup de production que nous restaurerons sur la recette avec un PITR défini par les équipes métier (restore database with norecovery, restore log with norecovery stopat….)

Une fois cette base restaurée, on regarde les users attachés à cette base. On voit bien qu’un user “manu” est présent:

 

 

Mais si l’on repasse les mêmes requêtes qu’au début pour voir les SID du login et du user :

 

use master
select name, sid from sys.syslogins where name =’manu’
go

use capdata_db
select name, sid from sys.sysusers where name=’manu’
go

Cette fois ci, les SID sont différents.

 

 

En regardant le mapping de notre login “manu” on voit qu’il a perdu son attachement à la base “capdata_db” via le user “manu”, et surtout que ce mapping ne peut plus se faire :

 

 

Le mapping entre le login “manu” et le user “manu” attribué à la base n’est plus possible car un user de même nom existe déjà. Nous aurions pu passer par un nouveau user, par exemple “manu2” mais cela a pour conséquence la perte des informations et des droits qui auraient été configurés en base.
S’il l’on édite le script lié à ce processus, on voit en fait que le moteur SQL Server tente, par défaut, de créer un nouveau user, et d’y attacher les droits adéquat.

 

USE [capdata_db]
GO
CREATE USER [manu] FOR LOGIN [manu]
GO
USE [capdata_db]
GO
ALTER USER [manu] WITH DEFAULT_SCHEMA=[dbo]
GO

 

Il ne sera donc pas possible de modifier un user via ALTER USER pour resynchroniser avec un login même si celui ci est de même nom

 

La création d’un nouveau login

L’étape de resynchronisation login/user va nécessiter la recréation du login auparavant utilisé. Nous pourrons générer le DDL de création de ce login via l’option “Script login As \ Create to” afin de reprendre les informations complète du login.

DROP LOGIN [manu]
GO

CREATE LOGIN [manu] WITH PASSWORD=N’P0wsbkDGuBz8zmXSg/Jrk/fE5T4yzfBeTLpMIODi1t8=’,
DEFAULT_DATABASE=[master],
DEFAULT_LANGUAGE=[us_english],
CHECK_EXPIRATION=OFF,
CHECK_POLICY=ON
GO

 

On ira modifier l’odre SQL ci dessus en y ajoutant l’option ‘SID=’ avec la valeure relevé auparavant qui correspond à celle du user défini en base.

Dans notre cas, nous avions, après restauration de la base

 

La requête sera donc la suivante

CREATE LOGIN [manu] WITH PASSWORD=N’P0wsbkDGuBz8zmXSg/Jrk/fE5T4yzfBeTLpMIODi1t8=’,
SID=0xE19A6699AF282B47AF3CCE8E235447D7,
DEFAULT_DATABASE=[master],
DEFAULT_LANGUAGE=[us_english],
CHECK_EXPIRATION=OFF,
CHECK_POLICY=ON
GO

On se chargera de changer le mot de passer avec l’ancienne valeur si celui ci a été laissé par défaut par l’ordre CREATE LOGIN. Une fois le login recréé, on verra que dans les propriétés de celui-ci, le mapping au user “manu” de la base “capdata_db” est effectué de façon automatique

 

 

Mais surtout, que son SID a changé et correspond à celui du user en base

 

use master
select name, sid from sys.syslogins where name =’manu’
go

use capdata_db
select name, sid from sys.sysusers where name=’manu’
go

 

 

Les différentes entrées suivantes présentes sur l’error_log devraient maintenant ne plus apparaître

 

2019-03-18 19:17:14.840 Logon Login failed for user ‘manu’. Reason: Failed to open the explicitly specified database ‘capdata_db’. [CLIENT: *******]
2019-03-18 19:17:14.890 Logon Error: 18456, Severity: 14, State: 38.
2019-03-18 19:17:14.890 Logon Login failed for user ‘manu’. Reason: Failed to open the explicitly specified database ‘capdata_db’. [CLIENT: *******]
2019-03-18 19:17:14.890 Logon Error: 18456, Severity: 14, State: 38.

 

🙂

N’hésitez pas à laisser un commentaire !

Continuez votre lecture sur le blog :

• AWS : Backup Restore SQL Server RDS vers une EC2 ou On-Premise et vice versa ! (Emmanuel RAMI) [AWSSQL Server]
• Le chiffrement Oracle : native network encryption (Emmanuel RAMI) [Oracle]
• Retrouver la requête à l’origine d’une erreur 8623 “The query processor ran out of internal resources and could not produce a query plan” (David Baffaleuf) [SQL Server]
• SQL Server 2022 Backup / Restore via un bucket S3 sous AWS (Louis PROU) [AWSSQL Server]
• Réplication MySQL : Resynchronisation d’un Slave MySQL (Capdata team) [MySQL]