· 8 years ago · Apr 23, 2018, 07:50 PM
1$Conn=New-Object System.Data.SqlClient.SQLConnection
2$QueryTimeout = 120
3$ConnectionTimeout = 30
4
5###########################################################
6# Execute Query function
7###########################################################
8Function executequery($Query, $QueryTimeout, $ServerName)
9{
10 $Datatable = New-Object System.Data.DataTable
11 $ConnectionString = "Server={0};Database={1};Integrated Security=True;Connect Timeout={2}" -f $ServerName,$Database,$ConnectionTimeout
12 $Conn.ConnectionString=$ConnectionString
13 $Cmd=New-Object system.Data.SqlClient.SqlCommand($Query,$Conn)
14 $Cmd.CommandTimeout=$QueryTimeout
15
16 do
17 {
18 $Conn.Open()
19 Start-Sleep -Seconds 2
20 }while ($Conn.State -ne 'Open')
21
22 $Reader = $cmd.ExecuteReader()
23 $Datatable.Load($Reader)
24 $Conn.Close()
25 return $Datatable
26}
27
28
29###########################################################
30# Create spHexaDecimal Stored Procedure
31###########################################################
32
33Function CreatespHexaDecimal ($ServerName)
34 {
35 $Query='USE [master];
36 GO
37 SET ANSI_NULLS ON;
38 GO
39 SET QUOTED_IDENTIFIER ON;
40 GO
41 CREATE PROCEDURE [dbo].[spHexaDecimal]
42 (
43 @BinValue VARBINARY(256)
44 , @HexValue VARCHAR(514) OUTPUT
45 )
46 AS
47
48 DECLARE @CharValue VARCHAR(514)
49 DECLARE @i INT
50 DECLARE @Length INT
51 DECLARE @HexString CHAR(16)
52
53 SET @CharValue = ''0x''
54 SET @i = 1
55 SET @Length = DATALENGTH(@BinValue)
56 SET @HexString = ''0123456789ABCDEF''
57
58 WHILE (@i <= @Length)
59 BEGIN
60
61 DECLARE @TempInt INT
62 DECLARE @FirstInt INT
63 DECLARE @SecondInt INT
64
65 SET @TempInt = CONVERT(INT, SUBSTRING(@BinValue, @i, 1))
66 SET @FirstInt = FLOOR(@TempInt/16)
67 SET @SecondInt = @TempInt - (@FirstInt * 16)
68 SET @CharValue = @CharValue
69 + SUBSTRING(@HexString, @FirstInt + 1, 1)
70 + SUBSTRING(@HexString, @SecondInt + 1, 1)
71
72 SET @i = @i + 1
73
74 END --WHILE (@i <= @Length)
75
76 SET @HexValue = @CharValue'
77
78 Invoke-Sqlcmd -Query $Query -ServerInstance $ServerName
79 }
80
81
82###########################################################
83# CheckStroedProc
84###########################################################
85
86Function CheckStoredProc ($Server)
87{
88 $Query= 'SELECT 1 AS ExistCheck
89 FROM sysobjects
90 WHERE id = object_id(N''[dbo].[spHexaDecimal]'')
91 AND OBJECTPROPERTY(id, N''IsProcedure'') = 1 '
92
93 $Result=executequery $Query $QueryTimeout $Server
94 $Exist=$Result | SELECT -ExpandProperty ExistCheck
95 IF ($Exist -ne 1)
96 {
97 CreatespHexaDecimal -ServerName $Server
98 }
99}
100
101###########################################################
102# Get Login Script
103###########################################################
104
105Function Get-Script ($Server)
106{
107
108$Query='DECLARE @TempTable TABLE
109(Script NVARCHAR(MAX))
110DECLARE @Login NVARCHAR (MAX)
111DECLARE CURLOGIN CURSOR FOR
112SELECT name
113FROM sys.server_principals
114WHERE CONVERT(VARCHAR(24),create_date,103) = CONVERT(VARCHAR(24),GETDATE(),103)
115 OR CONVERT(VARCHAR(24),modify_date,103) = CONVERT(VARCHAR(24),GETDATE(),103)
116
117OPEN CURLOGIN
118 FETCH NEXT FROM CURLOGIN INTO @Login
119
120WHILE @@FETCH_STATUS = 0
121BEGIN
122 SET NOCOUNT ON
123 DECLARE @Script NVARCHAR (MAX)
124 DECLARE @LoginName VARCHAR(500)= @Login
125 DECLARE @LoginSID VARBINARY(85)
126 DECLARE @SID_String VARCHAR(514)
127 DECLARE @LoginPWD VARBINARY(256)
128 DECLARE @PWD_String VARCHAR(514)
129 DECLARE @LoginType CHAR(1)
130 DECLARE @is_disabled BIT
131 DECLARE @default_database_name SYSNAME
132 DECLARE @default_language_name SYSNAME
133 DECLARE @is_policy_checked BIT
134 DECLARE @is_expiration_checked BIT
135 DECLARE @createdDateTime DATETIME
136
137
138
139 SELECT @LoginSID = P.[sid]
140 , @LoginType = P.[type]
141 , @is_disabled = P.is_disabled
142 , @default_database_name = P.default_database_name
143 , @default_language_name = P.default_language_name
144 , @createdDateTime = P.create_date
145 FROM sys.server_principals P
146 WHERE P.name = @LoginName
147
148 /** Some Output **/
149 SET @Script = ''''
150
151
152
153
154 --If the login is a SQL Login, then do a lot of stuff...
155 IF @LoginType = ''S''
156 BEGIN
157
158 SET @LoginPWD = CAST(LOGINPROPERTY(@LoginName, ''PasswordHash'') AS VARBINARY(256))
159
160 EXEC spHexaDecimal @LoginPWD, @PWD_String OUT
161 EXEC spHexaDecimal @LoginSID, @SID_String OUT
162
163 SELECT @is_policy_checked = S.is_policy_checked
164 , @is_expiration_checked = S.is_expiration_checked
165 FROM sys.sql_logins S
166
167 /** Create Script **/
168 SET @Script = @Script + CHAR(13) + CHAR(13)
169 + ''IF EXISTS (SELECT name FROM sys.server_principals WHERE name= ''''''+ @LoginName + '''''') ''
170 + CHAR(13) + '' BEGIN ''
171 + CHAR(13) + CHAR(9) + '' ALTER LOGIN '' + QUOTENAME(@LoginName)
172 + CHAR(13) + CHAR(9) + ''WITH PASSWORD = '' + @PWD_String + '' HASHED''
173 + CHAR(13) + CHAR(9) + '', DEFAULT_DATABASE = ['' + @default_database_name + '']''
174 + CHAR(13) + CHAR(9) + '', DEFAULT_LANGUAGE = ['' + @default_language_name + '']''
175 + CHAR(13) + CHAR(9) + '', CHECK_POLICY '' + CASE WHEN @is_policy_checked = 0 THEN ''=OFF'' ELSE ''=ON'' END
176 + CHAR(13) + CHAR(9) + '', CHECK_EXPIRATION '' + CASE WHEN @is_expiration_checked = 0 THEN ''=OFF'' ELSE ''=ON'' END
177 + CHAR(13) + '' END ''
178 + CHAR(13) + ''ELSE''
179 + CHAR(13) + '' BEGIN ''
180 + CHAR(13) + CHAR(9) + '' CREATE LOGIN '' + QUOTENAME(@LoginName)
181 + CHAR(13) + CHAR(9) + ''WITH PASSWORD = '' + @PWD_String + '' HASHED''
182 + CHAR(13) + CHAR(9) + '', SID = '' + @SID_String
183 + CHAR(13) + CHAR(9) + '', DEFAULT_DATABASE = ['' + @default_database_name + '']''
184 + CHAR(13) + CHAR(9) + '', DEFAULT_LANGUAGE = ['' + @default_language_name + '']''
185 + CHAR(13) + CHAR(9) + '', CHECK_POLICY '' + CASE WHEN @is_policy_checked = 0 THEN ''=OFF'' ELSE ''=ON'' END
186 + CHAR(13) + CHAR(9) + '', CHECK_EXPIRATION '' + CASE WHEN @is_expiration_checked = 0 THEN ''=OFF'' ELSE ''=ON'' END
187 + CHAR(13) + '' END ''
188
189 SET @Script = @Script + CHAR(13) + CHAR(13)
190 + '' ALTER LOGIN ['' + @LoginName + '']''
191 + CHAR(13) + CHAR(9) + ''WITH DEFAULT_DATABASE = ['' + @default_database_name + '']''
192 + CHAR(13) + CHAR(9) + '', DEFAULT_LANGUAGE = ['' + @default_language_name + '']''
193
194 END
195 ELSE
196 BEGIN
197
198 --The login is a NT login (or group).
199 SET @Script = @Script + CHAR(13) + CHAR(13)
200 + ''IF NOT EXISTS (SELECT name FROM sys.server_principals WHERE name= ''''''+ @LoginName + '''''') ''
201 + CHAR(13) + '' BEGIN ''
202 + CHAR(13) + CHAR(9) + '' CREATE LOGIN '' + QUOTENAME(@LoginName) + '' FROM WINDOWS''
203 + CHAR(13) + CHAR(9) + ''WITH DEFAULT_DATABASE = ['' + @default_database_name + '']''
204 + CHAR(13) + '' END ''
205 END
206
207 /******************************************************************************************/
208 --This section deals with the Server Roles that belong to that login...
209 /******************************************************************************************/
210
211 DECLARE @ServerRoles TABLE
212 (
213 ServerRole SYSNAME
214 , MemberName SYSNAME
215 , MemberSID VARBINARY(85)
216 )
217
218 INSERT INTO @ServerRoles EXEC sp_helpsrvrolemember
219
220 --Remove all Roles
221 SET @Script = @Script + CHAR(13)
222 SET @Script = @Script
223 + CHAR(13) + ''EXEC sp_dropsrvrolemember '' + QUOTENAME(@LoginName, '''''''') + '', '' + ''''''sysadmin''''''
224 + CHAR(13) + ''EXEC sp_dropsrvrolemember '' + QUOTENAME(@LoginName, '''''''') + '', '' + ''''''securityadmin''''''
225 + CHAR(13) + ''EXEC sp_dropsrvrolemember '' + QUOTENAME(@LoginName, '''''''') + '', '' + ''''''serveradmin''''''
226 + CHAR(13) + ''EXEC sp_dropsrvrolemember '' + QUOTENAME(@LoginName, '''''''') + '', '' + ''''''setupadmin''''''
227 + CHAR(13) + ''EXEC sp_dropsrvrolemember '' + QUOTENAME(@LoginName, '''''''') + '', '' + ''''''processadmin''''''
228 + CHAR(13) + ''EXEC sp_dropsrvrolemember '' + QUOTENAME(@LoginName, '''''''') + '', '' + ''''''diskadmin''''''
229 + CHAR(13) + ''EXEC sp_dropsrvrolemember '' + QUOTENAME(@LoginName, '''''''') + '', '' + ''''''dbcreator''''''
230 + CHAR(13) + ''EXEC sp_dropsrvrolemember '' + QUOTENAME(@LoginName, '''''''') + '', '' + ''''''bulkadmin''''''
231
232 /** Output to script... **/
233 --SET @Script = @Script + CHAR(13) + CHAR(13)
234
235 --Test if there are any server roles for this login...
236 IF EXISTS(SELECT 1 FROM @ServerRoles WHERE MemberName = @LoginName)
237 BEGIN
238
239 SET @Script = @Script + CHAR(13)
240
241 DECLARE @ServerRole SYSNAME
242 DECLARE curRoles CURSOR LOCAL STATIC FORWARD_ONLY
243
244 FOR SELECT ServerRole
245 FROM @ServerRoles
246 WHERE MemberName = @LoginName
247
248 OPEN curRoles
249
250 FETCH NEXT FROM curRoles
251 INTO @ServerRole
252
253 WHILE @@FETCH_STATUS = 0
254 BEGIN
255
256 /** Output to Script **/
257 SET @Script = @Script
258 + CHAR(13) + ''EXEC sp_addsrvrolemember '' + QUOTENAME(@LoginName, '''''''') + '', '' + QUOTENAME(@ServerRole, '''''''')
259
260 FETCH NEXT FROM curRoles
261 INTO @ServerRole
262
263 END
264
265 --Cleanup.
266 CLOSE curRoles
267 DEALLOCATE curRoles
268
269 END
270 INSERT INTO @TempTable
271 VALUES(@Script)
272
273 FETCH NEXT FROM CURLOGIN INTO @Login
274END
275CLOSE CURLOGIN;
276DEALLOCATE CURLOGIN;
277SELECT Script FROM @TempTable'
278
279$Result=executequery $Query $QueryTimeout $Server
280
281If($Result -eq $null)
282 {
283 break
284 }
285Else
286 {
287 [Void][System.IO.Directory]::CreateDirectory("C:temp")
288 $Path = "C:temp"
289 $Acl = (Get-Item $Path).GetAccessControl('Access')
290 $Username = Get-WmiObject win32_service | Where name -EQ 'SQLSERVERAGENT' | Select -ExpandProperty StartName
291 $Ar = New-Object System.Security.AccessControl.FileSystemAccessRule($Username, 'Full', 'ContainerInherit,ObjectInherit', 'None', 'Allow')
292 $Acl.SetAccessRule($Ar)
293 Set-Acl -path $Path -AclObject $Acl
294 $Result | select -ExpandProperty Script | Out-File C:tempScript.txt
295 }
296}
297
298
299###########################################################
300# SCRIPT BODY
301###########################################################
302
303$Query= "SELECT ISNULL(SERVERPROPERTY ('InstanceName'), 'DEFAULT') InstanceName
304 , name AGName
305 , replica_server_name Replica
306 , role_desc
307 FROM sys.dm_hadr_availability_replica_states hars
308 INNER JOIN sys.availability_groups ag ON ag.group_id = hars.group_id
309 INNER JOIN sys.availability_replicas ar ON ar.replica_id = hars.replica_id
310 WHERE role_desc = 'PRIMARY'
311 ORDER BY role_desc asc"
312Write-Host "Is this Primary Replica?"
313$Result=executequery $Query $QueryTimeout $PrimaryReplica
314If ($Result -eq $null)
315 {
316 Write-Host "No, it's not."
317 break
318 }
319Else
320 {
321 Write-Host "Yes, it is."
322 $PrimaryReplica= $Result | select -ExpandProperty Replica
323 Write-Host "Check for prerequisite, if not present deploy it."
324 CheckStoredProc -Server $PrimaryReplica
325 Write-Host "Get script for new/modifies login(s)."
326 Get-Script -Server $PrimaryReplica
327
328 $Query= "SELECT ISNULL(SERVERPROPERTY ('InstanceName'), 'DEFAULT') InstanceName
329 , name AGName
330 , replica_server_name Replica
331 , role_desc
332 FROM sys.dm_hadr_availability_replica_states hars
333 INNER JOIN sys.availability_groups ag ON ag.group_id = hars.group_id
334 INNER JOIN sys.availability_replicas ar ON ar.replica_id = hars.replica_id
335 WHERE role_desc = 'SECONDARY'
336 ORDER BY role_desc asc"
337
338 $Result=executequery $Query $QueryTimeout $PrimaryReplica
339 $SecondaryReplicas= $Result | select -ExpandProperty Replica
340 $Query= Get-Content -Path 'C:tempScript.txt' | Out-String
341 ForEach($SecondaryReplica in $SecondaryReplicas)
342 {
343 Invoke-Sqlcmd -Query $Query -ServerInstance $SecondaryReplica
344 Write-Host "Successfully copied login(s) to $SecondaryReplica"
345 }
346 Remove-Item C:tempScript.txt
347 }