Powershell script, AddMRUsers_CU14.ps1
#new guid for each user
clear
$usersList =Get-Content "C:\Users\administrator.CONTOSO\Desktop\userslist.txt"
$MRDBName="ManagementReporter"
$MRServerName="MRSERVER2012"
function Call-ADO
{
param(
[string]$cmd,
$SQLNamePlusInstance = ".",
[string]$databasename
)
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$Error.Clear()
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = 'Server=' + $SQLNamePlusInstance + ';Persist Security Info=False;Integrated Security=SSPI' + ';DataBase=' + $databasename
$SqlCmd.Connection = $SqlConnection
$SqlCmd.CommandText = $cmd
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
$DataSet = New-Object System.Data.DataSet
$RowCount = $SqlAdapter.Fill($DataSet)
$SqlConnection.Close()
if ($Error.Count -gt 0){
$Error
}
else
{
return $DataSet
}
}
foreach( $u in $usersList)
{
$firstCommaIndex =$u.IndexOf(',')
$fullName = $u.Substring(0,$firstCommaIndex).Trim()
$secondCommaIndex = $u.IndexOf(',',($firstCommaIndex+1))
$domainName = $u.Substring(($firstCommaIndex+1),($secondCommaIndex-$firstCommaIndex-1)).Trim()
$thirdCommaIndex = $u.IndexOf(',',($secondCommaIndex+1))
$role = $u.Substring($secondCommaIndex+1).Trim()
$userGUID= [System.Guid]::NewGuid().ToString().ToUpper()
$SecurityPrincipalInsert = "insert into reporting.SecurityPrincipal values ('" + $userGUID + "', '"+$domainName +"', '',0,0)"
$SecurityUserInsert1 = "insert into reporting.SecurityUser values('"+$userGUID + "','"+$fullName+"',"
$objUser = New-Object System.Security.Principal.NTAccount($domainName)
$strSID = $objUser.Translate([System.Security.Principal.SecurityIdentifier])
$usersSID = $strSID.Value
$roleID = 0
$d = Get-Date
switch ($role)
{
'Administrator' { $roleID = 5 }
'Designer' { $roleID = 4 }
'Generator' { $roleID = 3 }
'Viewer' { $roleID = 2 }
default { $roleID = 5 }
}
$SecurityUserInsert2 = "$roleID, '',0x,0x,'"+$usersSID+"','',0,'"+ $d.ToShortDateString() + "','9999-12-31', 0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,NULL)"
$SecurityUserInsert = $SecurityUserInsert1 + $SecurityUserInsert2
$Error.Clear()
$v = Call-ADO $SecurityPrincipalInsert $MRServerName $MRDBName
$v = Call-ADO $SecurityUserInsert $MRServerName $MRDBName
if($Error.Count -gt 0)
{
Write-Host $domainName " insert failed"
$Error
}
else
{
Write-Host $domainName " insert successful"
}
#$SecurityPrincipalInsert
#$SecurityUserInsert
}
Share
Report
394
All responses (
Answers (
