How to use Powershell to modify SQL login permission?

Question

I already used Microsoft.SqlServer.Management.Smo.Login in Powershell to create SQL-login with no problem.

Just for the new SQL-login created, how could I use Powershell to set its permissions as image show below? (I want to set "View any database" to be "Deny").

Thanks heaps!

enter image description here

How about using TSQL? You'd find a lot more help for using GRANT and DENY statements instead of messing around with management objects. — vonPryz
– vonPryz, Commented Jan 9, 2014 at 8:11

Chad Miller · Accepted Answer · 2014-01-09 11:39:59Z

1

#Valid serverpermissions:
#AdministerBulkOperations AlterAnyConnection AlterAnyCredential AlterAnyDatabase AlterAnyEndpoint AlterAnyEventNotification
#AlterAnyLinkedServer AlterAnyLogin AlterAnyServerAudit   AlterResources AlterServerState AlterSettings AlterTrace AuthenticateServer
#ConnectSql ControlServer CreateAnyDatabase CreateDdlEventNotification CreateEndpoint CreateTraceEventNotification ExternalAccessAssembly
#UnsafeAssembly ViewAnyDatabase ViewAnyDefinition ViewServerState 

    #Set variables
    $ServerInstance = ""
    $name = ""
    $action = ""

    $server = new-object ("Microsoft.SqlServer.Management.Smo.Server") $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) }
        }

answered Jan 9, 2014 at 11:39

Chad Miller

42.4k4 gold badges33 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Samuel Over a year ago

Hi Chad, thanks a lot. It works. BTW, where could I find the document about these library?

Chad Miller Over a year ago

The code about is based on the sqlpsx CodePlex project in Module\SqlServer\SqlServer.psm1. For in on SMO classes MSDN lists properties and methods, but can be tricky to find--for example how do you know server perms are server and not login method.

Slogmeister Extraordinaire · Accepted Answer · 2018-06-11 15:02:56Z

0

Assuming the SQL PowerShell module is loaded, I use this:

$Instance=Get-Item -Path "SQLSERVER:\SQL\$env:COMPUTERNAME\<instance name>"
$DBPermSet=[Microsoft.SqlServer.Management.Smo.ServerPermissionSet]::new()
$DBPermSet.Add([Microsoft.SqlServer.Management.Smo.ServerPermission]::ViewAnyDatabase)
$Instance.Deny($DBPermSet,"<user name>")

answered Jun 11, 2018 at 15:02

Slogmeister Extraordinaire

3,2462 gold badges32 silver badges45 bronze badges

Collectives™ on Stack Overflow

How to use Powershell to modify SQL login permission?

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related