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!!