Discussion :

[vince2005]

Bonjour,

J'aurai 1 question à vous poser :
J'ai une base "Client" sur mon serveur "P1" que je souhaite copier sur mon environnement de recette "R1". Cette base peut exister ou avoir été supprimer au préalable.
Une fois rechargé, j'ai une désynchronisation au niveau des users et des comptes de connexion à ma base Client, notamment avec mon user que nous appellerons "toto".

Tout d'abord :
A partir de quel base, je trouve mes comptes orphelins : la base master ou Client ? Et si j'en ai, que puis je faire comme commande.

J'ai vu qu'il existait :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
 
sp_change_users_login 'report'
go
 
sp_change_users_login

Pourriez vous me donner un exemple et me dire à partir de quel base, dois je lancer ces commandes ?
Comment puis je passer cela dans un script.

Merci pour votre retour.

[mikedavem]

Hello,

Une fois rechargé, j'ai une désynchronisation au niveau des users et des comptes de connexion à ma base Client, notamment avec mon user que nous appellerons "toto".

En effet c'est normal d'avoir ce comportement. Sur SQL Server un utilisateur de bases de données est associé à un login au niveau instance. Lorsque tu migres ta base de données, il faut recréer les logins correspondants sur l'instance cible.

En fonction de la version, tu peux aussi lancer le script suivant dans le contexte de la bases de données que tu viens de migrer:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

ALTER USER <user> WITH LOGIN = <login>;

Une autre solution est de recréer tes logins sur le serveur cible avec le même SID que ceux existant sur le serveur source. Si tu es dans le cas d'un login de type Windows alors pas de souci car le SID qui permet de mapper ton login à un utilisateur de bases de données ne changera pas d'un environnement SQL à un autre car il est géré directement depuis Windows. En revanche si tu es dans le cas d'un utilisateur de type SQL, le SID du nouveau login créé ne va pas correspondre à celui de l'utilisateur de bases de données que tu transfères. le SID est régénéré à chaque création de login et différent d'une instance à une autre. Dans ce cas tu peux recréer les logins concernés avec le même SID généré sur le serveur source de la manière suivante:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
-- Récupération SID
select 
	name, sid
from sys.server_principals
where name = '<user>';
-- SID = 0xE33710CD150703479D5F71546901E909
 
-- Création du login avec le SID récupéré plus haut
create login <user> 
with password = '<pwd>', sid = 0xE33710CD150703479D5F71546901E909, check_policy = off;
go

Ensuite tu peux transférer ta base de données de la production vers l'environnement de recette sans avoir à exécuter d'étapes supplémentaires de remapping login <-> user

++

[vince2005]

Si on parle le même langage, les logins se rapportent à l'instance et les Users à la base ?

Une fois importé ma base "Client" sur mon serveur cible, si je lance la requete ci-dessous, j'obtiens mes users orphelins de ma base client avec leur SID :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
 
use client
go
sp_change_users_login 'report'
go

Il suffit alors de faire simplement le user orphelin + le sid et de lancer ta requête :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
 
-- Création du login avec le SID récupéré plus haut
create login <user> 
with password = '<pwd>', sid = 0xE33710CD150703479D5F71546901E909, check_policy = off;
go

Au lieu de supprimer et recréer les logins ? n'est-il pas plus judicieux de supprimer les users.

Merci pour vos conseils.

[vince2005]

Quel est l'intérêt de la requête :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
 
sp_change_users_login 'AUTO_FIX', 'admtoto', NULL, 'passwordtoto'

[mikedavem]

Quel est l'intérêt de la requête :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
 
sp_change_users_login 'AUTO_FIX', 'admtoto', NULL, 'passwordtoto'

De fixer rapidement et automatiquement un utilisateur orphelin en créant le login associé avec le même nom. Cependant tu perds tout ta sécurité niveau serveur (appartenance aux rôles de serveurs etc ...).

++