In this recipe, we will walk through creating a custom database role.
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]
Let's explore the steps for creating a custom database role:
Open PowerShell ISE as an administrator.
Import the
SQLPSmodule and create a new SMO Server Object:#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 run:
$databasename = "AdventureWorks2014" ...