Create Logins, Users and provide Schema based access using Roles in Azure SQL...

This post includes

- How to create a Role

- How to create Logins

- Map Users with Logins

- Provide different Schema based access for the users

Different Roles are added to provide the different level access.

This post discusses about providing the below types of access

- Grant Select for the objects with Schema 'ABC' (Read Access)

- Grant Select and Update for the objects with Schema 'ABC' (Read Update Access)

- Grant Select, Update and Insert for the objects with Schema 'ABC' (Read Update Write Access)

- Grant Select, Update, Insert and Delete for the objects with Schema 'ABC' (Full Access)

1. Create Roles

First those roles needs to be create using the below script

CREATE ROLE ABC_ReadAccss;

GRANT SELECT ON SCHEMA::ABC TO ABC_ReadAccss;

GO

 

CREATE ROLE ABC_ReadUpdateAccss;

GRANT SELECT, UPDATE ON SCHEMA::ABC TO ABC_ReadUpdateAccss;

GO

CREATE ROLE ABC_ReadUpdateWriteAccss;

GRANT SELECT, UPDATE, WRITE ON SCHEMA::ABC TO ABC_ReadUpdateWriteAccss;

GO

 

CREATE ROLE ABC_FullAccess;

GRANT SELECT, INSERT, UPDATE, DELETE, EXECUTE ON SCHEMA::STOCKODDS TO ABC_FullAccess;

GO

 

2. Create Logins

Four Logins are created to demonstrate the usage of those four different access levels

CREATE LOGIN [ABC_ReadAccss_Login] WITH PASSWORD=N'1qazwsx'
GO

CREATE LOGIN [ABC_ReadUpdateAccss_Login] WITH PASSWORD=N'6gh38nns'
GO

CREATE LOGIN [ABC_ReadUpdateWriteAccss_Login] WITH PASSWORD=N'9uhv7fcxx'

GO 

CREATE LOGIN [ABC_FullAccess_Login] WITH PASSWORD=N'5tgb8txx'

GO 

3. Create Users

CREATE USER [ABC_ReadAccss_User] FOR LOGIN [ABC_ReadAccss_Login] WITH DEFAULT_SCHEMA=[ABC]

GO

CREATE USER [ABC_ReadUpdateAccss_User] FOR LOGIN [ABC_ReadUpdateAccss_Login] WITH DEFAULT_SCHEMA=[ABC]

GO

CREATE USER [ABC_ReadUpdateWriteAccss_User] FOR LOGIN [ABC_ReadUpdateWriteAccss_Login] WITH DEFAULT_SCHEMA=[ABC]

GO

CREATE USER [ABC_FullAccess_User] FOR LOGIN [ABC_FullAccess_Login] WITH DEFAULT_SCHEMA=[ABC]

GO

4. Add Users to Roles

ALTER ROLE ABC_ReadAccss ADD MEMBER ABC_ReadAccss_User

GO

 

ALTER ROLE ABC_ReadUpdateAccss ADD MEMBER ABC_ReadUpdateAccss_User

GO

 

ALTER ROLE ABC_ReadUpdateWriteAccss ADD MEMBER ABC_ReadUpdateWriteAccss_User

GO

ALTER ROLE ABC_FullAccess ADD MEMBER ABC_FullAccess_User

GO

Hope this will be useful...