Discussion :

[Nelsonovitch]

Bonjour à tous,

nous avons mis en place un groupe de "haute disponibilité" contenant 2 serveurs sous SQL SERVER 2012.

Les réplications des tables se font sans soucis par contre nous souhaiterions répliquer les comptes de connexion AD d'un serveur à un autre.

Existe-t-il un script pour ce type de tâche (mon binôme et moi-même sommes en début d'immersion dans le monde SQL ) ?

Merci d'avance.

[mikedavem]

Des scripts tout fait je n'ai pas vu mais certainement en cherchant un peu sur la toile. Il faudra adapter pour le contexte AlwaysOn (détection si on est sur le réplica primaire et synchronisation vers les secondaires). Il y a toutes les DMV AlwaysOn pour faire cela

++

[Nelsonovitch]

Bonjour,

tout d'abord merci pour votre message.
J'avoue avoir cherché sur le net mais à part des scripts de création de comptes de connexion je n'ai rien trouvé (surement que je m'y prend mal ) par rapport à ce que l'on souhaite mettre en place.

[mikedavem]

Tu peux te baser sur le script fourni par Microsoft ici. Cep

Tu peux également passer par PowerShell également. Les cmdlets et objets SMO peuvent t'aider à cette tâche.

Il faudra également veilleur à récupérer les droits de niveau serveur dans les scripts.

++

[Nelsonovitch]

Merci infiniment pour tes réponses !

[elsuket]

Compliquée la procédure de Microsoft lorsqu'on est sous SQL Server 2005 ou plus !

Scripter les logins :

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
	SELECT	'CREATE LOGIN [' + name + '] FROM WINDOWS' AS sql_statement
	FROM	sys.server_principals
	WHERE	type_desc IN ('WINDOWS_LOGIN', 'WINDOWS_GROUP')
	AND	is_disabled = 0
	AND	name NOT IN ('NT AUTHORITY\SYSTEM', '<autres>')
	AND	name NOT LIKE '%sqlserveragent%'
UNION ALL
	SELECT	'CREATE LOGIN ' + name
			+ ' WITH PASSWORD = ' + CONVERT(varchar(max), password_hash, 1) + ' HASHED' 
			+ ', SID = ' + CONVERT(varchar(max), sid, 1)
		AS sql_statement
	FROM	sys.sql_logins
	WHERE	type_desc IN ('WINDOWS_LOGIN', 'SQL_LOGIN')
	AND	is_disabled = 0
	AND	name NOT IN ('sa', 'NT AUTHORITY\SYSTEM')
ORDER	BY sql_statement

Scripter les utilisateurs et leurs droits :

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
-- roles
SELECT	'CREATE ROLE [' + name + '] AUTHORIZATION dbo' AS create_role_statement
FROM	sys.database_principals
WHERE	type_desc = 'DATABASE_ROLE'
AND	name NOT LIKE 'MS%' COLLATE SQL_Latin1_General_CP1_CS_AS
AND	name NOT IN ('public')
AND	is_fixed_role = 0
ORDER	BY name
 
-- users
SELECT	'CREATE USER [' + name + '] FOR LOGIN [' + name  + ']' AS create_user_statement
FROM	sys.database_principals
WHERE	type_desc IN ('SQL_USER', 'WINDOWS_USER')
AND	name NOT IN ('dbo', 'guest', 'sys', 'INFORMATION_SCHEMA')
ORDER	BY name
 
-- role members
SELECT		'EXEC sp_addrolemember ''' + R.name + ''', ''' + M.name + '''' AS role_members_statement
FROM		sys.database_role_members AS RM
INNER JOIN	sys.database_principals AS R
			ON R.principal_id = RM.role_principal_id
INNER JOIN	sys.database_principals AS M
			ON M.principal_id = RM.member_principal_id
WHERE		R.name NOT LIKE 'MS%' COLLATE SQL_Latin1_General_CP1_CS_AS
AND		M.name <> 'dbo'
ORDER BY	role_members_statement
 
-- role grants
;WITH
	CTE AS
	(
		SELECT		R.name AS role_name
				, G.permission_name
				, CASE G.class
					WHEN 1 THEN O.name
					WHEN 3 THEN S.name
					WHEN 0 THEN DB_NAME()
				END AS object_name
				, CASE G.class
					WHEN 1 THEN O.type_desc
					WHEN 3 THEN 'SCHEMA'
					WHEN 0 THEN 'DATABASE'
				END AS object_type_desc
		FROM		sys.database_principals AS R
		INNER JOIN	sys.database_permissions AS G
					ON G.grantee_principal_id = R.principal_id
		LEFT JOIN	sys.schemas AS S
					ON G.major_id = S.schema_id
		LEFT JOIN	sys.objects AS O
					ON G.major_id = O.object_id
		WHERE		R.name <> 'public'
		AND		G.permission_name <> 'CONNECT'
		--ORDER BY	R.name, object_name, G.permission_name
	)
SELECT	'GRANT ' COLLATE database_default + permission_name
	+ ' ON ' + CASE WHEN object_name IS NULL THEN 'SCHEMA::dbo TO [' ELSE object_name + ' TO [' END
	+ role_name + ']' AS role_grant_statement
FROM	CTE
 
-- user grants
;WITH
	CTE AS
	(
		SELECT		DB_USER.name AS user_name
				, DB_GRANT.class_desc
				, DB_GRANT.state_desc
				, DB_GRANT.permission_name
				, CASE DB_GRANT.class_desc
					WHEN 'OBJECT_OR_COLUMN' THEN O.name
					WHEN 'SCHEMA' THEN S.name
					WHEN 'TYPE' THEN TY.name
					ELSE O.name
				END AS object_name
				, O.type_desc AS DB_object_type
		FROM		sys.database_permissions AS DB_GRANT
		INNER JOIN	sys.database_principals AS DB_USER
					ON DB_GRANT.grantee_principal_id = DB_USER.principal_id
		LEFT JOIN	sys.objects AS O
					ON O.object_id = DB_GRANT.major_id
		LEFT JOIN	sys.types AS TY
					ON TY.user_type_id = DB_GRANT.major_id
		LEFT JOIN	sys.schemas AS S
					ON S.schema_id = DB_GRANT.major_id
		WHERE		DB_USER.name <> 'public'
		AND		DB_GRANT.permission_name <> 'CONNECT'
	)
SELECT	state_desc COLLATE database_default + ' ' + permission_name
	+ CASE
		WHEN object_name IS NULL THEN ''
		ELSE ' ON ' + object_name
	END + ' TO [' + user_name  + ']' AS create_user_statement
FROM	CTE
ORDER	BY create_user_statement

Y'a plus qu'à récupérer et exécuter

@++

[mikedavem]

Pour les groupes de disponibilités pas besoin de créer les utilisateurs.

En revanche il te manque le script de création des droits de niveau serveur (hors rôles de serveur). Comme tu es motivé je te laisse le faire

Après il faut adapter pour la synchro en fonction du contexte. Si les réplicas sont utilisés en lecture seule par exemple pas question tout supprimer puis tout récréer ....

++