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. Then, we will grant it SELECT permissions to the HumanResources schema, and ALTER and CREATE TABLE permissions to the database.

Here's the T-SQL equivalent of what we are trying to accomplish:

USE AdventureWorks2014
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...

Let's explore the steps for creating a custom database role:

  1. Open PowerShell ISE as an administrator.
  2. Import the SQLPS module and create a new SMO Server Object:
    #import SQL Server module Import-Module SQLPS -DisableNameChecking ...

Get SQL Server 2014 with PowerShell v5 Cookbook now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial