How do change a users permission to connect to database engine through powershell in ssms?-CodePudding

I'm currently writing a script to add users automatically. So far I can add a new SQL Login with the sqlserver modules Add-SqlLogin cmddlet. And I can add the database mapping for the user with another script I've found. What remains is to actually allow the user to connect to the database (see image). I haven't found a way to do that through powershell yet. Can anyone point me to a solution? The option I wish to set through powershell

CodePudding user response：

After another hour of googling I have found a solution:

$ServerInstance = "server"
$name = "user"
$action = "Grant"
$permission = "ConnectSql"

$server =  New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server -ArgumentList $ServerInstance

$perm = New-Object Microsoft.SqlServer.Management.Smo.ServerPermissionSet
$perm.$($permission.ToString()) = $true

switch ($action)
{ 
    'Grant'  { $server.Grant($perm,$name) }
    'Deny'   { $server.Deny($perm,$name) }
    'Revoke' { $server.Revoke($perm,$name) }
}

How to use Powershell to modify SQL login permission? as answered by Chad Miller.