如何检查SQL Server登录名是否具有角色“SysAdmin”

Question

我正在编写一个PowerShell脚本，该脚本检查指定的SQL Server，以查看是否已成功地将特定登录名(让我们称之为'ContractUser')授予了SysAdmin角色。我正在寻找一种方法来编写一个简单的if/then检查，以查看该登录是否具有SysAdmin角色。

我在网上找到了许多示例，这些示例展示了如何检索整个SysAdmin登录列表，但还没有弄清楚如何将这些脚本更改为只检查服务器的单个登录。

$SQLSvr = "SQLServername\Instancename";
$MySQL = New-Object Microsoft.SqlServer.Management.Smo.Server $SQLSvr;
$SQLLogins = $MySQL.Logins;

$SysAdmins = $null;
$SysAdmins = foreach ($SQLUser in $SQLLogins) {
    foreach ($role in $SQLUser.ListMembers()) {
        if ($role -match 'sysadmin') {
            ...do stuff...
        };
    };
};

我尝试对此稍作修改，并编写了以下代码，它没有按预期返回结果(我知道我正在检查的服务器具有该登录名，并且该登录名在sysadmin中，但脚本说明并非如此)：

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoExtended") | Out-Null
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo") | Out-Null

$serverConnection = New-Object Microsoft.SqlServer.Management.Common.ServerConnection
$serverConnection.ServerInstance=$SQLServer
$server = New-Object Microsoft.SqlServer.Management.SMO.Server($serverConnection)
$SQLLogins = $server.Logins
$loginIsSysadmin = "No"
foreach ($SQLLogin in $SQLLogins) {
    $SQLRoles = $SQLLogin.ListMembers()
    if ($SQLRoles -eq "SysAdmin" -and $SQLLogin.name -eq "ContractUser") {
        $loginIsSysadmin = "Yes"
        break
    }
}

但是，在运行脚本后，该变量仍为"No“。

任何关于我做错了什么的意见都将非常感谢。

Answer 1

与登录的直接IS_SRVROLEMEMBER查询相比，sql (Transact-SQL)可能提供了一种更全面的方法。例如，它检查：

1. 登录名是否存在，如果存在，则为sysadmin的直接成员或间接成员。如果登录名不存在，则为
2. ；如果登录名是本身映射到登录名的Windows组(或组的组)的间接成员，则为sysadmin成员。

https://docs.microsoft.com/en-us/sql/t-sql/functions/is-srvrolemember-transact-sql?view=sql-server-ver15

请参见下面的示例用法：

function QueryDatabaseScript($databaseServer, $dbScript, $timeout)
{
    # Query and return recordset array for passed T-SQL string
    $SQLConnection = New-Object System.Data.SqlClient.SqlConnection
    $SQLConnection.ConnectionString = "server=$($databaseServer);Initial Catalog=master;Integrated Security=True;"

    try
    {        
        $SQLConnection.Open()
        $SQLCommand = New-Object System.Data.SqlClient.SqlCommand
        $SQLCommand.Connection = $SQLConnection 
        $SQLCommand.CommandTimeout = $timeout
        
        $SQLCommand.CommandText = $dbScript
        $reader = $SQLCommand.ExecuteReader()

        $recordsetArray = @()
        while ($reader.Read())
        {
            $recordsetArray += $reader[0]
        }

        $SQLConnection.Close()
        $SQLConnection.Dispose()
    }
    catch
    {
        write-host "QueryDatabaseScript failed.`nError: $($_.Exception.Message)"
        $SQLConnection.Dispose()
        throw $_
    }

    return $recordsetArray
}

$sqlSvr = 'SQLServername\Instancename';

$queryResults = QueryDatabaseScript $sqlSvr "select Is_SrvRoleMember('sysadmin')" 0
$isSysadmin = $queryResults[0]
write-host "Current user is a sysadmin: $isSysadmin"

$sqlLogin = 'ContractUser'
$queryResults = QueryDatabaseScript $sqlSvr "select Is_SrvRoleMember('sysadmin', '$sqlLogin')" 0
$isSysadmin = $queryResults[0]
write-host "$sqlLogin is a sysadmin: $isSysadmin"

Answer 2

在这里，像往常一样，invoke-sqlcmd是您的朋友。SMO并非如此。

例如

$login = 'sa'
$sql = @"
    select r.name role_name, m.name member_name
    from sys.server_principals r
    join sys.server_role_members rm
      on r.principal_id = rm.role_principal_id
    join sys.server_principals m
      on m.principal_id = rm.member_principal_id
    where m.name = '$login'
"@
$isSysadmin = ( @(invoke-sqlcmd $sql ).Count -eq 1 )
write-host "Is a sysadmin: $isSysadmin"