Discussion :

[scazikiss]

bonjour,


j'ai ce petit souci apres chaque restauration( sqlserver 2005) .
apres avoir rafraichit une base que j'appelle B1, je n'arrive pas à me logguer avec un compte qui se trouve dans security=>logins . A chque fois il me dit que "le compte existe deja" et dans ce cas je dois supprimer le compte en question dans security=> Users et là je peux mapper la base B1.
j'ai du manquer une coche lors de la restauration...? avez vous une idée..?
merci beaucoup.
Cdt.

[SQLpro]

Normal les comptes de connexion se connecte à un serveur, pas à une base. Ils sont donc recensés dans la base master du serveur d'origine.

Si vous voulez retrouver ce login sur le nouveau serveur sur lequel vous avez appliqué la restauration il faut le recréer.

Dans mon livre que j'écris sur SQL server, je donne ce script pour migrer les comptes de connexion propre à une base :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
USE ???;
GO
 
/******************************************************************************
* SCRIPT DE RECRÉATION DES COMPTES DE CONNEXION ET REMAPPING AUX UTILISATEURS *
*******************************************************************************
* Frédéric Brouard SQLpro - www.sqlspot.com - sqlpro@sqlspot.com - 2012-01-24 *
*******************************************************************************
* ce script SQL recré les comptes de connexion Windows et SQL, les rôles de   *
* base de données et les privilèges associées à ces comptes, et remappe le    *
* tout, et ceci pour les utilisateurs d'une base de données particulière      *
* passée en argument au niveau de la première ligne du script (USE ???)       *
* Le résultat de ce script doit être visualisé en mode texte pour pouvoir     *
* être utilisé.                                                               *
******************************************************************************/
 
DECLARE @T TABLE (ordre        smallint,
                  name         NVARCHAR(128),
                  type_desc    VARCHAR(16),
                  SQL_command  NVARCHAR(max));
 
INSERT INTO @T
-- Création des comptes de connexion SQL autres que "sa"
SELECT 1, sl.name, 'LOGIN_SQL', 'CREATE LOGIN [' 
       + sl.name + '] WITH PASSWORD = ''******************'''
       + ', SID = ' + master.S_REVERT_LOGINS.F_HEX_TO_STRING(sid) 
       + ', DEFAULT_DATABASE = [' + default_database_name + '] ' 
       + ', DEFAULT_LANGUAGE  = [' + default_language_name + '] '
       + ', CHECK_EXPIRATION = ' + CASE is_expiration_checked 
                                      WHEN 1 THEN 'ON' 
                                      ELSE 'OFF' 
                                   END
       + ', CHECK_POLICY = ' + CASE is_policy_checked 
                                  WHEN 1 THEN 'ON' 
                                  ELSE 'OFF' 
                               END  
       + COALESCE(', CREDENTIAL  = [' + c.name + '] ', '')
       + ';'                                                                
FROM   sys.sql_logins AS sl 
       LEFT OUTER JOIN sys.credentials AS c
            ON sl.credential_id = c.credential_id       
WHERE sl.name NOT IN ('sa') AND sl.name NOT LIKE '##MS?_%' ESCAPE '?';
 
INSERT INTO @T
-- Création des comptes de connexion Windows autres que purement système
SELECT 2, name, 'LOGIN_SYS', 'CREATE LOGIN [' + name + '] FROM WINDOWS WITH DEFAULT_DATABASE=[' 
       + default_database_name +'], DEFAULT_LANGUAGE=[' 
       + default_language_name + '];' 
FROM   sys.server_principals
WHERE  type_desc LIKE 'WINDOWS?_%' ESCAPE '?' 
  AND  name NOT LIKE '%\MSSQL$'    
            + RIGHT(@@SERVERNAME, CHARINDEX('\', @@SERVERNAME)+1)
  AND  name NOT LIKE '%\SQLAgent$' 
            + RIGHT(@@SERVERNAME, CHARINDEX('\', @@SERVERNAME)+1);
 
INSERT INTO @T
-- ajout des roles de serveur liés aux comptes de connexion
SELECT 3, spm.name, 'ROLE', 
       'EXEC sp_addsrvrolemember ''' + spm.name +''', ''' + spr.name +''';'
FROM   sys.server_role_members  AS srm
       INNER JOIN sys.server_principals AS spm
             ON srm.member_principal_id = spm.principal_id
       INNER JOIN sys.server_principals AS spr
             ON srm.role_principal_id = spr.principal_id            
WHERE  spr.type_desc = 'SERVER_ROLE'
  AND  spm.name IN (SELECT name 
                    FROM   @T 
                    WHERE  type_desc LIKE 'LOGIN?_%' ESCAPE '?');
 
INSERT INTO @T
-- récupération des privilèges de serveur
SELECT 4, gte.name, 'PRIVILEGE',
       'EXECUTE AS LOGIN = ''' + gto.name + ''';GRANT ' + permission_name 
       + CASE WHEN minor_id = 0 AND major_id <> 0  
                 THEN ' ON LOGIN::[' + (SELECT s.name 
                                        FROM   sys.server_principals AS s 
                                        WHERE  s.principal_id = major_id) +']' 
              WHEN minor_id <> 0 AND major_id <> 0 
                 THEN ' ON [' + (SELECT s.name 
                                 FROM   sys.schemas AS s 
                                 WHERE  s.schema_id = minor_id) +'].['
                              + (SELECT o.name 
                                 FROM   sys.objects AS o 
                                 WHERE  o.object_id = major_id) + ']'
              ELSE ''
         END         
       + ' TO [' + gte.name +']; REVERT; ' COLLATE French_BIN
FROM   sys.server_permissions AS sp
       INNER JOIN sys.server_principals AS gto
             ON sp.grantor_principal_id = gto.principal_id       
       INNER JOIN sys.server_principals AS gte
             ON sp.grantee_principal_id = gte.principal_id
WHERE  gte.name IN (SELECT name 
                    FROM   @T 
                    WHERE  type_desc LIKE 'LOGIN?_%' ESCAPE '?');  
 
INSERT INTO @T
-- remapping utilisateur / connexion
SELECT DISTINCT 5, T.name, 'MAPPING', 
       'ALTER USER [' + dp.name + '] WITH LOGIN = ['+ T.name +'];'
FROM   @T AS T
       INNER JOIN sys.server_principals AS sp
             ON T.name = sp.name
       INNER JOIN sys.database_principals AS dp 
             ON dp.sid = sp.sid;
 
 
-- affichage du résultat
SELECT SQL_command + CHAR(13) + CHAR(10) + N'GO'
FROM   @T 
WHERE  name IN (SELECT sp.name
                FROM   sys.database_principals AS dp 
                       INNER JOIN sys.server_principals AS sp ON dp.sid = sp.sid)
ORDER BY ordre;

IMPORTANT :
• remplacez ??? dans la première ligne du script par le nom de la base cible.
• lancez ce script sur le serveur source après avoir demandé le résultat sous forme de texte et non en grille

A +

[scazikiss]

d'abord merci de votre réponse rapide.
j'ai essayé d"exécuter la premiere partie du script et j'ai eu l'erreur suivante:
sauriez vous pour quelle raison..?

Msg 4121, Level 16, State 1, Line 7
Cannot find either column "master" or the user-defined function or aggregate "master.S_REVERT_LOGINS.F_HEX_TO_STRING", or the name is ambiguous.

1ere partie du script:

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
USE BO;
GO
 
DECLARE @T TABLE (ordre        smallint,
                  name         NVARCHAR(128),
                  type_desc    VARCHAR(16),
                  SQL_command  NVARCHAR(max));
 
INSERT INTO @T
-- Création des comptes de connexion SQL autres que "sa"
SELECT 1, sl.name, 'LOGIN_SQL', 'CREATE LOGIN [' 
       + sl.name + '] WITH PASSWORD = ''******************'''
       + ', SID = ' + master.S_REVERT_LOGINS.F_HEX_TO_STRING(sid) 
       + ', DEFAULT_DATABASE = [' + default_database_name + '] ' 
       + ', DEFAULT_LANGUAGE  = [' + default_language_name + '] '
       + ', CHECK_EXPIRATION = ' + CASE is_expiration_checked 
                                      WHEN 1 THEN 'ON' 
                                      ELSE 'OFF' 
                                   END
       + ', CHECK_POLICY = ' + CASE is_policy_checked 
                                  WHEN 1 THEN 'ON' 
                                  ELSE 'OFF' 
                               END  
       + COALESCE(', CREDENTIAL  = [' + c.name + '] ', '')
       + ';'                                                                
FROM   sys.sql_logins AS sl 
       LEFT OUTER JOIN sys.credentials AS c
            ON sl.credential_id = c.credential_id       
WHERE sl.name NOT IN ('sa') AND sl.name NOT LIKE '##MS?_%' ESCAPE '?';

[scazikiss]

en fait j'ai retiré la ligne qui produit l'erreur
" + ', SID = ' + master.S_REVERT_LOGINS.F_HEX_TO_STRING(sid) "

et avant d'exécuter la partie du script j'ai pris soin de cliquer sur 'result to text'
je n'ai plus l'erreur mais je n'obtiens pas l'affichage qui devait etre.
j'ai du manquer quelque chose.
merci de votre aide.
Cdt.

[scazikiss]

vraiment désolé, je n'obtiens pas l'affichage c'est parce que j'ai oublié la requete " select SQL_command + CHAR(13) + CHAR(10) + N'GO from @T "
par contre si je rajoutes la ligne
" + ', SID = ' + master.S_REVERT_LOGINS.F_HEX_TO_STRING(sid) "
j'ai toujours l'erreur :

Cannot find either column "master" or the user-defined function or aggregate "master.S_REVERT_LOGINS.F_HEX_TO_STRING", or the name is ambiguous.

Cdt.

[mikedavem]

j'ai ce petit souci apres chaque restauration( sqlserver 2005) .
apres avoir rafraichit une base que j'appelle B1, je n'arrive pas à me logguer avec un compte qui se trouve dans security=>logins . A chque fois il me dit que "le compte existe deja" et dans ce cas je dois supprimer le compte en question dans security=> Users et là je peux mapper la base B1.

Ceci est normal si tu passes par l'interface graphique. En effet lorsque tu mappes ton login à un utilisateur dans la base de données concernée, SQL Server tente de créer l'utilisateur avant de l'associer. C'est la raison pour laquelle tu as ce message d'erreur.

Tu peux utiliser la commande suivante pour régénérer le mapping entre tes 2 utilisateurs :

Code :

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

1
2
3
4
USE maBase;
GO
 
ALTER USEr monUser WITH LOGIN = monLogin

++

[scazikiss]

merci à tous les 2 pour votre aide.
Cdt.

[SQLpro]

Désolé j'étais en intervention et il manquait à cette procédure une fonction que vioci :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
USE master;
GO
 
CREATE SCHEMA S_REVERT_LOGINS;
GO
 
CREATE FUNCTION S_REVERT_LOGINS.F_HEX_TO_STRING (@binvalue varbinary(256))
RETURNS varchar (514)
AS
BEGIN
    DECLARE @hexvalue  varchar (514), 
            @charvalue varchar (514),
            @i         int, 
            @length    int, 
            @hexstring char(16),
            @tempint   int, 
            @firstint  int,
            @secondint int;
    SELECT @charvalue = '0x', 
           @i = 1, 
           @length = DATALENGTH (@binvalue), 
           @hexstring = '0123456789ABCDEF';
    WHILE (@i <= @length)
    BEGIN
        SET @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1));
        SET @firstint = FLOOR(@tempint/16);
        SET @secondint = @tempint - (@firstint*16);
        SET @charvalue = @charvalue 
                       + SUBSTRING(@hexstring, @firstint+1, 1) 
                       + SUBSTRING(@hexstring, @secondint+1, 1);
        SET @i = @i + 1;
    END
    RETURN @charvalue;
END;
GO

A +