Générer un script de création des comptes de connexion

1 réponse

Lors de la restauration d’une base sur un autre serveur, les comptes de connexion ne sont pas recrées sauf si vous restaurez la base master sur ce même serveur. Ce outil vous permet de générer un script SQL de recréation des comptes de connexion en vu de les régénérer sur le serveur de destination de la restauration.

Les comptes de connexion spécifiques à une base de données doivent être présent sur le serveur cible de la restauration. Or, comme ils sont situés dans la base master, il faut les extraire du serveur source pour les recréer dans le serveur cible. La base source que l’on veut restaurer doit être présente et fonctionnelle sur le serveur source. C’est pour cela qu’il convient de créer ses scripts en amont, par exemple dans le cadre d’un plan de sauvegarde plus général.
Pour ce faire, l’outil de reverse engineering doit prendre en compte :
• les comptes de connexion purement SQL;
• les comptes de connexion système Windows;
• les rôles prédéfinis de serveur associés à ces comptes;
• les privilèges de niveau serveur associés à ces comptes.
Et effectuer un remapping de ces comptes aux utilisateurs de chaque base de données.

ATTENTION :

  • si le serveur cible est en mode de sécurité « Windows » et que des comptes SQL doivent être créés, veillez à activer la sécurité mixte.
  • dans les scripts générés par cet outil, les mots de passe doivent être exprimés en clair à la place des étoiles (******************)

Les scripts SQL de l’outil de reverse engineering sont les suivants :

1 РFonction pour cr̩er une chaine de caract̬res SQL repr̩sentant une donn̩e hexad̩cimale

Fonction (UDF) scalaire pour créer une chaine de caractères SQL représentant un VARBINAY d’après une donnée hexadécimale. La fonction est à créer une fois pour toute.

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

2 Рscript de g̩n̩ration des commandes de recr̩ation des comptes de connexion

Script de génération des commandes de recréation des comptes de connexion.

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
Jouez le script résultant sur le serveur cible, après avoir restauré la base cible.

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;

NOTA : pour 2012 il faudrait rajouter la prise en compte des rôles de serveur nouveauté de la version. En sus il est désormais possible de faire figurer les comptes de connexion au sein de la base de production même, via la notion de « contained database ».

Le site web sur le SQL et les SGBDR

MVP Microsoft SQL Server



Frédéric Brouard, alias SQLpro, ARCHITECTE DE DONNÉES

Expert  S.G.B.D  relationnelles   et   langage  S.Q.L

Moste  Valuable  Professionnal  Microsoft  SQL Server

Société SQLspot  :  modélisation, conseil, formation,

optimisation,  audit,  tuning,  administration  SGBDR

Enseignant: CNAM PACA, ISEN Toulon, CESI Aix en Prov.

L’ntreprise SQL Spot

Une réflexion au sujet de « Générer un script de création des comptes de connexion »

Laisser un commentaire