This recipe shows how to assign permissions to a database user via SMO and PowerShell.
In this recipe, we will use the AdventureWorks2014 database user eric we created in previous recipes. We will grant this user the ALTER and CREATE TABLE permissions. Here's the T-SQL equivalent of what we are trying to accomplish:
USE [AdventureWorks2014] GO GRANT ALTER, CREATE TABLE TO [eric]
You can substitute this database user with any database user that you already have in your database.
To assign permissions and roles to a database user, let's follow these steps:
Open PowerShell ISE as an administrator.
Import the
SQLPSmodule and create a new SMO Server Object as follows:#import SQL Server module Import-Module SQLPS -DisableNameChecking #replace this with your instance name $instanceName = "localhost" $server = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server -ArgumentList $instanceName
Add the following script and...