Dear DBA!! I am a DB_READER & I ‘ONLY’ want to create a View, nothing else

This article is a classic scenario, where a developer comes to a DBA requesting for some special but safe privileges in a database, which is not a straightforward activity. In my case, these are the lines from my developer: ‘I am currently a DB_READER on the database SQLSERVICE. Please create a new schema named AZURE_EXPERTS and I need view creation privileges on that newly created schema. I shouldn’t have any other privileges than the requested ‘.

To give some background, all the tables in the database SQLSERVICE are on the default schema dbo. The following queries are self-explanatory, and it should solve your purpose. But, there is a twist in the tale.

USE [SQLSERVICE]

GO

CREATE SCHEMA [AZURE_EXPERTS] --THE NEW SCHEMA CREATION

GO

CREATE ROLE ViewCreation; --CREATING A ROLE TO ADD THE USER

GO

GRANT CREATE VIEW TO ViewCreation; --GRANTING THE CREATE VIEW PERMISSIONS

GO

EXEC sp_addrolemember 'ViewCreation', 'DEVELOPER'; --ADDING MY DEVELOPER TO THE ROLE VIEW CREATION

GO

--RULE OF THE THUMB IS THAT YOU CANNOT CREATE A VIEW WITH OUT HAVING ALTER PRIVILEGES ON THE SCHEMA

GRANT ALTER ON SCHEMA::AZURE_EXPERTS TO ViewCreation;

GO

--NOW TEST YOUR WORK

EXECUTE AS USER = 'DEVELOPER';

GO

CREATE VIEW AZURE_EXPERTS.AES_KEYS AS SELECT KEY_ID FROM [dbo].[MY_PERSONALINFO]

GO

REVERT;

GO

As the user ‘DEVELOPER’ has DB_READER privileges on the database, we are not providing GRANT SELECT ON the schemas.

Twist in the tale

 As we have provided the ‘ALTER’ privileges on the newly created schema for the Role ViewCreation, our user ‘DEVELOPER’ can drop and alter the tables in the newly created schema. But, my developer isn’t expecting this. To counter this, we now create a trigger to avoid the drop and alter permissions on the tables.

USE [SQLSERVICE]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TRIGGER [SaveMyDeveloper]

ON DATABASE

FOR DROP_TABLE, ALTER_TABLE

AS

BEGIN

IF IS_MEMBER('ViewCreation') = 1

BEGIN

PRINT 'You are restricted to alter or drop a table.';

ROLLBACK TRANSACTION;

END;

END;

GO

ENABLE TRIGGER [SaveMyDeveloper] ON DATABASE

GO

Now, my dear DBA: you gave yourself very fewer chances of hearing a developer say ‘Oh my God!!! I accidentally deleted a table ☹ ‘.

Hope you enjoyed this one!!