I’ve been working with Powershell lately, and I wrote a couple of functions to help verify server permissions in Microsoft SQL Server.

Why would I want to verify server permissions instead of verifying server roles? In my case, I’m interested in knowing if a particular user is able to create a database on a SQL Server. While the dbcreator role does allow a user to create a database, there are other roles that will also allow a user to create a database, such as sysadmin. Additionally, if a user is a member of an AD Group that has the role assigned, then the role is not directly assigned to the user. In the end, I found it simplest to check for the actual CREATE ANY DATABASE permission.

In this post, we will see how to check if the current AD user has a specific server permission and how to check if an AD account other than the current user has a specific server permission.

I realize that you can use Invoke-Sqlcmd for these queries, but from what I’ve read, you have to install the full SQL Management tools to get that cmdlet. The scripts shown here use classes in the System.Data namespace in lieu of the SQL Server cmdlets, and as such, it can run on any Windows computer without any installation beyond Powershell.

Current User has Permission

This first function will tell us if the currently logged in user has a specific server-level permission on a SQL server.

# Runs a SQL query and lets us know if there is at least 1
# row returned from the query.
function SQLResultsExist($connStr, $sql) {
  $da = new-object System.Data.SqlClient.SqlDataAdapter `
    ($sql, $connStr)
  $da.SelectCommand.CommandTimeout = 10
  $dt = new-object System.Data.DataTable
  $da.fill($dt) | out-null
  return $dt.Rows.Count -gt 0
}

function CurrUserHasPermission($connStr, $permission) {
  Try {
    $sql = "SELECT TOP 1 permission_name
            FROM fn_my_permissions(NULL, 'SERVER')
            WHERE permission_name = '$permission'"

    # True if any records exist
    $hasPermission = SQLResultsExist $connStr $sql
    return $hasPermission
  } Catch {
    # If the user can't connect to the database server at
    # all, we'll end up here
    return $false
  }
}

$connStr = `
  "server=localhost;database=master;Integrated Security=sspi"
$permission = "CREATE ANY DATABASE"
$hasPermission = CurrUserHasPermission($connStr, $permission)

We are making use of the SQL Server function fn_my_permissions, which will tell a user what permissions have been assigned to them. Note that we have passed in SERVER as a parameter, because we are inquiring about server level permissions. You can also pass in DATABASE if you are interested in database permissions, and there are other types of permissions listed in the fn_my_permissions documentation.

Arbitrary User has Permission

The next function will tell us if an arbitrary AD user (a user different from the current user) has a specific server permission on a SQL database server. This is a little tricky, because the user running the script might have no permissions on the SQL Server, and thus may not be able to connect to the SQL Server at all.

To get around this, we’ll launch a process in the context of the other user, then use the CurrUserHasPermissions function we defined above.

function UserHasPermission($username, $securePassword, `
    $connStr, $perm){

  # Turn on powershell remoting, if it isn't on yet
  Enable-PSRemoting -Force

  # We need to update the Sddl policy on the local system so
  # that $username is allowed to do local Powershell Remoting
  $origSddl = (Get-PSSessionConfiguration `
    -Name "Microsoft.PowerShell").SecurityDescriptorSDDL
  $newSddl = AddUserToSddl $origSddl $username
  Set-PSSessionConfiguration -name "Microsoft.PowerShell" `
    -SecurityDescriptorSddl $newSDDL -force 

  # SQLUtils.ps1 should contain CurrUserHasPermission
  $sqlUtilsPath = "C:\scripts\SQLUtils.ps1"

  $crd =New-Object System.Management.Automation.PSCredential `
    -ArgumentList @($username,$securePassword)

  $result = Invoke-Command -Credential $crd `
    -ArgumentList ($connStr,$perm,$MyInvocation.ScriptName) `
    -ComputerName localhost -ScriptBlock {
        param($connStr, $permission, $sqlUtilsPath)
        . $sqlUtilsPath;
        return CurrUserHasPermission $connStr $permission;
    }

  # Now we'll restore the sddl to what it was originally
  Set-PSSessionConfiguration -name "Microsoft.PowerShell" `
    -SecurityDescriptorSddl $origSDDL -force 

  return $result
}

Invoke-Command allows us to execute a block of code under a different user context. This allows us to connect to the SQL Server as the other user. Note that since the codeblock in Invoke-Command needs the same variables from the rest of the Powershell script, we have to pass in those values as parameters, just as if the block of code was a separate ps1 file. Incidentally, that block of code can be in a separate ps1 file if you use the FilePath switch instead of the ScriptBlock switch.

When using Invoke-Command, you can return an arbitrary object and assign it to $result as seen above.

And here is the implementation of AddUserToSddl (from Scripting Guy)

function addUserToSddl([string]$sddl, [string]$username) {
  $isContainer = $false
  $isDS = $false

  $SecurityDescriptor = New-Object `
    -TypeName Security.AccessControl.CommonSecurityDescriptor`
    -ArgumentList $isContainer,$isDS, $sddl

  $sid = (New-Object Security.Principal.NTAccount $username) `
    .Translate([Security.Principal.SecurityIdentifier]).Value

  $accessType = "Allow"
  $accessMask = 268435456
  $inheritanceFlags = "none"
  $propagationFlags = "none"

  $SecurityDescriptor.DiscretionaryAcl.AddAccess($accessType,`
    $sid,$accessMask,$inheritanceFlags,$propagationFlags)

  return $SecurityDescriptor.GetSddlForm("All")
}

Once those methods are in place, you can check for permissions for the other AD user as follows:

$username = "CONTOSO\George.Smith"
# Escape any single or double quotes with a backtick
$securePassword = ConvertTo-SecureString "P@ssw0rd!" `
  -AsPlainText -Force

$connStr = `
  "server=localhost;database=master;Integrated Security=sspi"
$permission = "CREATE ANY DATABASE"

$hasPerm = UserHasPermission $username $securePassword `
  $connStr $permission

Resources: