/* 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_addrolememberN'YOURROLENAME', N'USERNAME1'
      GO
      EXEC sp_addrolememberN'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