Assigning permissions to a database user

This recipe shows how to assign permissions to a database user via SMO and PowerShell.

Getting ready

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.

How to do it...

To assign permissions and roles to a database user, let's follow these steps:

1. Open PowerShell ISE as an administrator.

2. Import the SQLPS module 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

3. Add the following script and...