Creating a database role

In this recipe, we will walk through creating a custom database role.

Getting ready

In this recipe, we will create a database role called Custom Role, and we will grant it SELECT permissions to the HumanResources schema, and ALTER and CREATE TABLE permissions to the database.

The T-SQL equivalent of what we are trying to accomplish is:

USE AdventureWorks2008R2
GO

CREATE ROLE [Custom Role]
GO

GRANT SELECT 
ON SCHEMA::[HumanResources] 
TO [Custom Role]

GRANT ALTER, CREATE TABLE 
TO [Custom Role]

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:

   #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...