SQL Script to Create Roles,Schemas, Add users and Associate Schema to Role

/* SQL Script to Create Roles, Schemas and users to the same */

--Creation of Database Role and Adding User's begin

USE [YOURDATABASENAME]

GO

CREATE ROLE [YOURROLENAME]

GO

      --Adding Users Begin

      EXEC sp_addrolemember N'YOURROLENAME', N'USERNAME1'

      GO

      EXEC sp_addrolemember N'YOURROLENAME', N'USERNAME2'

      GO

      --Adding Users End

      --Similarly Multiple users can be added using EXEC sp_addrolemember

--Creation of Database Role and Adding User's End

--Creation of Schema Starts

USE [YOURDATABASENAME]

GO

CREATE SCHEMA [YOURSCHEMANAME] AUTHORIZATION [YOURSCHEMANAME]

GO

--Creation of Schema End

--Association of Created Schema to Role Starts

USE [YOURDATABASENAME]

GO

ALTER AUTHORIZATION ON SCHEMA::[YOURSCHEMANAME] TO [YOURROLENAME]

GO

--Association of Created Schema to Role End