.

В объектной модели SQL Server Management (SMO) подключение к экземпляру SQL Server выполняется путем создания объекта Server. Одна из коллекций в объекте Server — коллекция Logins, содержащая набор объектов Login, по одному для каждого имени, определенного в экземпляре (рисунок 1).

 

Объектная модель SQL Server Management (SMO)
Рисунок 1. Объектная модель SQL Server Management (SMO)

Вы можете просмотреть свойства объекта Login, передав объект в команду Get-Member (см. экран).

 

Свойства объекта Login в команде Get-Member
Экран. Свойства объекта Login в команде Get-Member

Можно исключить имена регистрации в системе, проверив свойство IsSystemObject на значение $True и воспользовавшись свойствами Name и ID для проверки параметров безопасности.

Аналогично можно пройти по коллекции Databases и получить набор учетных записей пользователей в каждой базе данных (рисунок 2).

 

Получение информации о пользователях
Рисунок 2. Получение информации о пользователях

Интересно, что затем можно сравнить свойство Login объекта User базы данных со свойством Name объекта Login, чтобы связать имя регистрации SQL Server с соответствующими пользователями базы данных.

Для удаления имен регистрации, которые не являются именами регистрации в системе, не являются членами роли сервера sysadmin и не имеют пользователей базы данных, связанных с именем регистрации, следует сначала заполнить коллекцию подходящих имен регистрации.

$srv = new-object ('Microsoft.SqlServer.Management.Smo.Server') $inst
$logins = $srv.Logins
$log = @()
foreach ($l in $logins) {
$lnm=$l.Name
if (!($l.IsMember('sysadmin')) -and ($l.IsSystemObject -eq $False)) {
$lg=new-object system.object
$lg | Add-Member -type NoteProperty -name Name -value $lnm
$log += $lg
}
}

Затем следует подготовить другую коллекцию, содержащую имена регистрации, которым не соответствует никаких учетных записей пользователей базы данных. Для этого нужно пройти по коллекции подходящих имен регистрации и выполнить в коллекции Users в каждой базе данных поиск объекта User, где свойство User Login соответствует свойству Name для Login. Если такой объект обнаружен (получено действительное свойство ID для объекта User), следует присвоить переменной $lgused значение $true. Если после прохода по базам данных переменная по-прежнему имеет значение $False, добавьте ее в коллекцию $nodb.

$nodb = @()
foreach ($lg in $log) {
$lnm = $lg.Name
$lgused = $false
foreach ($db in $srv.Databases) {
$usr = $db.Users | where {$_.Login -eq $lnm}
$uid = [int]$usr.ID
if ($uid -gt 0) {
$lgused = $true
}
}
if ($lgused -eq $false) {
$n = new-object system.object
$n | Add-Member -type NoteProperty -name Name -value $lnm
$nodb += $n
}
}

Теперь, после заполнения коллекции $nodb, воспользуйтесь командой Write-Output, чтобы создать код T-SQL для удаления имен регистрации. Это делается для того, чтобы иметь возможность просмотреть список и убедиться, что каждое имя регистрации удаляется обоснованно. Существуют обстоятельства, когда некоторые имена лучше сохранить, поэтому вывод последнего фрагмента программного кода перенаправляется в файл, и после очистки я запускаю его в окне запросов в среде Management Studio.

foreach ($n in $nodb) {
$nm = $n.Name
write-output «DROP LOGIN [$nm]»
write-output «GO"
}

Учетные записи, утратившие связь с базой данных

После того, как имена регистрации приведены в порядок, займемся учетными записями пользователей, утратившими связь с базой данных. Можно пройти по списку баз данных, а затем по коллекции Users в каждой базе данных. Для каждого объекта User в коллекции (после тестирования, чтобы убедиться, что это не системный объект), можно просмотреть коллекцию Logins, в которой свойство Login объекта User соответствует свойству Name объекта Login. Если такого имени регистрации не существует, воспользуйтесь командой Write-Output, чтобы сформировать инструкцию DROP USER и избавиться от учетной записи пользователя базы данных. Я ввел небольшой дополнительный фрагмент программного кода, чтобы добавлять инструкцию USE базы данных каждый раз при изменении баз данных, но только если есть подходящая учетная запись пользователя для удаления.

$srv = new-object ('Microsoft.SqlServer.Management.Smo.Server') $inst
foreach ($db in $srv.Databases) {
$dbnm = $db.Name
$dbused = $False
foreach ($usr in $db.Users) {
$usrnm = $usr.Name
$ulog = $usr.Login
if ($usr.IsSystemObject -ne $True) {
$log = $srv.Logins | where {$_.Name -eq $ulog}
$lognm = $log.Name
if ($lognm -ne $ulog) {
if ($dbused -eq $False) {
$dbused = $True
write-output»USE [$dbnm]«
write-output»GO«
}
write-output»DROP USER [$usrnm]«
write-output»GO"
}
}
}
}

И опять же этот вывод предназначен для перенаправления в выходной sql-файл и выполнения в окне запросов в среде SQL Server Management Studio (SSMS) после просмотра.

При аккуратном использовании этот программный код поможет избавиться от бесполезных имен регистрации и учетных записей пользователей на серверах.

Поделитесь материалом с коллегами и друзьями

Купить номер с этой статьей в PDF