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_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
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s