Vous êtes ici :
Cette procédure stockée dba_CopyLogins permet de cloner les logins sql d’une instance à l’autre avec leur mot de passe et leur appartenance aux rôles et leurs permissions au niveau serveur. Le clonage des logins est une opération indispensable pour les scénarios de migration et de haute disponibilité (log shipping, mirroring, alwayson) afin de passer d’une instance à l’autre sans perte de mappage login/user.
La procédure doit être exécutée sur le serveur cible (celui sur lequel on souhaite créer les clones) et utilise un serveur lié vers le serveur source, qu’il faut créer préalablement.
L’auteur d’origine de ce script est Robert Davis dans son livre ‘Pro SQL Server Mirroring’.
Transact-SQL
|
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 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 338 339 340 341 342 343 344 345 346 347 |
/*------------------------------------------------------------------- [SCRIPT] dba_CopyLogins stored procedure [DATABASE] master [DESCRIPTION] Clone de logins avec rôles et permissions serveur, à lancer depuis la cible [PARAMETRES] @PartnerServer SYSNAME -- serveur lié existant, @Debug BIT -- si debug = 1 (par défaut), alors print seulement [AUTEUR] Robert DAVIS (Pro SQL Server Mirroring) [MAJ PAR] DATAFLY - Arian Papillon -------------------------------------------------------------------*/ USE master; Go IF EXISTS ( SELECT 1 FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = 'dba_CopyLogins' AND ROUTINE_SCHEMA = 'dbo' ) DROP PROCEDURE dbo.dba_CopyLogins GO SET ANSI_NULLS ON SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE dbo.dba_CopyLogins @PartnerServer SYSNAME , @Debug BIT = 1 AS DECLARE @MaxID INT , @CurrID INT , @SQL NVARCHAR(MAX) , @LoginName SYSNAME , @IsDisabled INT , @Type CHAR(1) , @SID VARBINARY(85) , @SIDString NVARCHAR(100) , @PasswordHash VARBINARY(256) , @PasswordHashString NVARCHAR(300) , @RoleName SYSNAME , @Machine SYSNAME , @PermState NVARCHAR(60) , @PermName SYSNAME , @Class TINYINT , @MajorID INT , @ErrNumber INT , @ErrSeverity INT , @ErrState INT , @ErrProcedure SYSNAME , @ErrLine INT , @ErrMsg NVARCHAR(2048) DECLARE @Logins TABLE ( LoginID INT IDENTITY(1, 1) NOT NULL PRIMARY KEY , [Name] SYSNAME NOT NULL , [SID] VARBINARY(85) NOT NULL , IsDisabled INT NOT NULL , [Type] CHAR(1) NOT NULL , PasswordHash VARBINARY(256) NULL ) DECLARE @Roles TABLE ( RoleID INT IDENTITY(1, 1) NOT NULL PRIMARY KEY , RoleName SYSNAME NOT NULL , LoginName SYSNAME NOT NULL ) DECLARE @Perms TABLE ( PermID INT IDENTITY(1, 1) NOT NULL PRIMARY KEY , LoginName SYSNAME NOT NULL , PermState NVARCHAR(60) NOT NULL , PermName SYSNAME NOT NULL , Class TINYINT NOT NULL , ClassDesc NVARCHAR(60) NOT NULL , MajorID INT NOT NULL , SubLoginName SYSNAME NULL , SubEndPointName SYSNAME NULL ) SET NoCount ON; IF CHARINDEX('\', @PartnerServer) > 0 BEGIN SET @Machine = LEFT(@PartnerServer, CHARINDEX('\', @PartnerServer) - 1); END ELSE BEGIN SET @Machine = @PartnerServer; END -- Get all Windows logins from principal server SET @SQL = 'Select P.name, P.sid, P.is_disabled, P.type, L.password_hash' + CHAR(10) + 'From ' + QUOTENAME(@PartnerServer) + '.master.sys.server_principals P' + CHAR(10) + 'Left Join ' + QUOTENAME(@PartnerServer) + '.master.sys.sql_logins L On L.principal_id = P.principal_id' + CHAR(10) + 'Where P.type In (''U'', ''G'', ''S'')' + CHAR(10) + 'And P.name <> ''sa''' + CHAR(10) + 'And P.name Not Like ''##%''' + CHAR(10) + 'And CharIndex(''' + @Machine + '\'', P.name) = 0;'; INSERT INTO @Logins ( Name , SID , IsDisabled , Type , PasswordHash ) EXEC sp_executesql @SQL; -- Get all roles from principal server SET @SQL = 'Select RoleP.name, LoginP.name' + CHAR(10) + 'From ' + QUOTENAME(@PartnerServer) + '.master.sys.server_role_members RM' + CHAR(10) + 'Inner Join ' + QUOTENAME(@PartnerServer) + '.master.sys.server_principals RoleP' + CHAR(10) + CHAR(9) + 'On RoleP.principal_id = RM.role_principal_id' + CHAR(10) + 'Inner Join ' + QUOTENAME(@PartnerServer) + '.master.sys.server_principals LoginP' + CHAR(10) + CHAR(9) + 'On LoginP.principal_id = RM.member_principal_id' + CHAR(10) + 'Where LoginP.type In (''U'', ''G'', ''S'')' + CHAR(10) + 'And LoginP.name <> ''sa''' + CHAR(10) + 'And LoginP.name Not Like ''##%''' + CHAR(10) + 'And RoleP.type = ''R''' + CHAR(10) + 'And CharIndex(''' + @Machine + '\'', LoginP.name) = 0;'; INSERT INTO @Roles ( RoleName, LoginName ) EXEC sp_executesql @SQL; -- Get all explicitly granted permissions SET @SQL = 'Select P.name Collate database_default,' + CHAR(10) + ' SP.state_desc, SP.permission_name, SP.class, SP.class_desc, SP.major_id,' + CHAR(10) + ' SubP.name Collate database_default,' + CHAR(10) + ' SubEP.name Collate database_default' + CHAR(10) + 'From ' + QUOTENAME(@PartnerServer) + '.master.sys.server_principals P' + CHAR(10) + 'Inner Join ' + QUOTENAME(@PartnerServer) + '.master.sys.server_permissions SP' + CHAR(10) + CHAR(9) + 'On SP.grantee_principal_id = P.principal_id' + CHAR(10) + 'Left Join ' + QUOTENAME(@PartnerServer) + '.master.sys.server_principals SubP' + CHAR(10) + CHAR(9) + 'On SubP.principal_id = SP.major_id And SP.class = 101' + CHAR(10) + 'Left Join ' + QUOTENAME(@PartnerServer) + '.master.sys.endpoints SubEP' + CHAR(10) + CHAR(9) + 'On SubEP.endpoint_id = SP.major_id And SP.class = 105' + CHAR(10) + 'Where P.type In (''U'', ''G'', ''S'')' + CHAR(10) + 'And P.name <> ''sa''' + CHAR(10) + 'And P.name Not Like ''##%''' + CHAR(10) + 'And CharIndex(''' + @Machine + '\'', P.name) = 0;' INSERT INTO @Perms ( LoginName , PermState , PermName , Class , ClassDesc , MajorID , SubLoginName , SubEndPointName ) EXEC sp_executesql @SQL; SELECT @MaxID = MAX(LoginID) , @CurrID = 1 FROM @Logins; WHILE @CurrID <= @MaxID BEGIN SELECT @LoginName = Name , @IsDisabled = IsDisabled , @Type = [Type] , @SID = [SID] , @PasswordHash = PasswordHash FROM @Logins WHERE LoginID = @CurrID; -- Create logins IF NOT EXISTS ( SELECT 1 FROM sys.server_principals WHERE name = @LoginName ) BEGIN SET @SQL = 'Create Login ' + QUOTENAME(@LoginName) IF @Type IN ( 'U', 'G' ) BEGIN SET @SQL = @SQL + ' From Windows;' END ELSE BEGIN SET @PasswordHashString = '0x' + CAST('' AS XML).value('xs:hexBinary(sql:variable("@PasswordHash"))', 'nvarchar(300)'); SET @SQL = @SQL + ' With Password = ' + @PasswordHashString + ' HASHED, '; SET @SIDString = '0x' + CAST('' AS XML).value('xs:hexBinary(sql:variable("@SID"))', 'nvarchar(100)'); SET @SQL = @SQL + 'SID = ' + @SIDString + ';'; END IF @Debug = 0 BEGIN BEGIN TRY EXEC sp_executesql @SQL; END TRY BEGIN CATCH SET @ErrNumber = ERROR_NUMBER(); SET @ErrSeverity = ERROR_SEVERITY(); SET @ErrState = ERROR_STATE(); SET @ErrProcedure = ERROR_PROCEDURE(); SET @ErrLine = ERROR_LINE(); SET @ErrMsg = ERROR_MESSAGE(); RAISERROR(@ErrMsg, 1, 1); END CATCH END ELSE BEGIN PRINT @SQL; END -- disable logins if necessary IF @IsDisabled = 1 BEGIN SET @SQL = 'Alter Login ' + QUOTENAME(@LoginName) + ' Disable;' IF @Debug = 0 BEGIN BEGIN TRY EXEC sp_executesql @SQL; END TRY BEGIN CATCH SET @ErrNumber = ERROR_NUMBER(); SET @ErrSeverity = ERROR_SEVERITY(); SET @ErrState = ERROR_STATE(); SET @ErrProcedure = ERROR_PROCEDURE(); SET @ErrLine = ERROR_LINE(); SET @ErrMsg = ERROR_MESSAGE(); RAISERROR(@ErrMsg, 1, 1); END CATCH END ELSE BEGIN PRINT @SQL; END END END SET @CurrID = @CurrID + 1; END -- Apply Roles SELECT @MaxID = MAX(RoleID) , @CurrID = 1 FROM @Roles; WHILE @CurrID <= @MaxID BEGIN SELECT @LoginName = LoginName , @RoleName = RoleName FROM @Roles WHERE RoleID = @CurrID; IF NOT EXISTS ( SELECT 1 FROM sys.server_role_members RM INNER JOIN sys.server_principals RoleP ON RoleP.principal_id = RM.role_principal_id INNER JOIN sys.server_principals LoginP ON LoginP.principal_id = RM.member_principal_id WHERE LoginP.type IN ( 'U', 'G', 'S' ) AND RoleP.type = 'R' AND RoleP.name = @RoleName AND LoginP.name = @LoginName ) BEGIN IF @Debug = 0 BEGIN EXEC sp_addsrvrolemember @rolename = @RoleName, @loginame = @LoginName; END ELSE BEGIN PRINT 'Exec sp_addsrvrolemember @rolename = ''' + @RoleName + ''','; PRINT ' @loginame = ''' + @LoginName + ''';'; END END SET @CurrID = @CurrID + 1; END SELECT @MaxID = MAX(PermID) , @CurrID = 1 FROM @Perms; WHILE @CurrID <= @MaxID BEGIN SELECT @PermState = PermState , @PermName = PermName , @Class = Class , @LoginName = LoginName , @MajorID = MajorID , @SQL = PermState + SPACE(1) + PermName + SPACE(1) + CASE Class WHEN 101 THEN 'On Login::' + QUOTENAME(SubLoginName) WHEN 105 THEN 'On ' + ClassDesc + '::' + QUOTENAME(SubEndPointName) ELSE '' END + ' To ' + QUOTENAME(LoginName) + ';' FROM @Perms WHERE PermID = @CurrID; IF NOT EXISTS ( SELECT 1 FROM sys.server_principals P INNER JOIN sys.server_permissions SP ON SP.grantee_principal_id = P.principal_id WHERE SP.state_desc = @PermState AND SP.permission_name = @PermName AND SP.class = @Class AND P.name = @LoginName AND SP.major_id = @MajorID ) BEGIN IF @Debug = 0 BEGIN BEGIN TRY EXEC sp_executesql @SQL; END TRY BEGIN CATCH SET @ErrNumber = ERROR_NUMBER(); SET @ErrSeverity = ERROR_SEVERITY(); SET @ErrState = ERROR_STATE(); SET @ErrProcedure = ERROR_PROCEDURE(); SET @ErrLine = ERROR_LINE(); SET @ErrMsg = ERROR_MESSAGE(); RAISERROR(@ErrMsg, 1, 1); END CATCH END ELSE BEGIN PRINT @SQL; END END SET @CurrID = @CurrID + 1; END SET NoCount OFF; GO |
Table of Contents