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 AdventureWorks2008R2 database user eric that we created in the previous recipes. We will grant this user ALTER and CREATE TABLE permissions. The T-SQL equivalent of what we are trying to accomplish is:

USE [AdventureWorks2008R2]
GO
GRANT 
  ALTER, 
  CREATE TABLE 
TO [eric]

You can substitute this with any database user you have in your database.

How to do it...

1. Open the PowerShell console by going to Start | Accessories | Windows PowerShell | Windows PowerShell ISE.

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 = "KERRIGAN"
   $server = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server -ArgumentList $instanceName

3. Add the following script and run it:

   $databasename = "AdventureWorks2008R2...