Giving special rights for group or user

SQL Server provides great flexibility for different types of security solutions.
In this post I want to show one of them.

Problem description:
1. Need to create a group/user "User1", which has to have only CRUD (Create-Read-Update-Delete) permissions for data in schema called "Schema1".
2. Need to create a group/user "User2", which has to have similar permissions as "User1" and have to be able create Views/Procedures/Functions in schema called "Schema2".
3. The group/user "User1" has to have Select/Execute permissions for all newly created objects in "Schema2".

Solution: Create a special database role for group/user "User2".

Here is how it can be done:

Preparation:

/* Create Test database */
use master;
GO
CREATE DATABASE TestPermissionDB;
GO
ALTER DATABASE [TestPermissionDB] SET CONTAINMENT = PARTIAL WITH NO_WAIT;
GO
USE TestPermissionDB;
GO

/* Create Test Schemas */
CREATE SCHEMA Schema1;
GO 
CREATE SCHEMA Schema2;
GO 
/* Create Test Users and give them CRUD permissions*/
CREATE USER User1 WITH PASSWORD=N'39+pjFkM6+9ll131C9RBWIYL4KcaFSHIqYwK16+B9ec=';
GO
CREATE USER User2 WITH PASSWORD=N'sw/Efa4VCM6bUrz5s+jl8zuRas5r6U8IP8eiUO83NTk=';
GO
ALTER ROLE [db_datareader] ADD MEMBER User1;
GO
ALTER ROLE [db_datawriter] ADD MEMBER User1;
GO
ALTER ROLE [db_datareader] ADD MEMBER User2;
GO
ALTER ROLE [db_datawriter] ADD MEMBER User2;
GO
/* Create Test table */
SELECT 'ABC' as Sample_Data
INTO Schema1.tbl_Sample_Table;
GO

Give special permissions for User1:

/* Grant Permissions for User1 */
GRANT EXECUTE TO User1;
GO

Create special Role:

/* Create special test role and give it specific permissions */
CREATE ROLE SpecialRole;
GO
GRANT CREATE PROCEDURE to SpecialRole;
GO
GRANT CREATE VIEW to SpecialRole;
GO
GRANT CREATE FUNCTION to SpecialRole;
GO

And here is the magic.

/* Associate special test role with Schema2 and assign User2 to that role */
ALTER AUTHORIZATION ON SCHEMA::Schema2 TO SpecialRole;
GO
ALTER ROLE SpecialRole ADD MEMBER User2;
GO

From this point User2 has all permissions it needs.

Test User2 Permissions:

/* test Permissions for User2*/
EXECUTE AS USER = 'User2' ;  
GO  
CREATE FUNCTION Schema2.fn_Test() RETURNS CHAR(3) as 
BEGIN
RETURN (SELECT TOP 1 Sample_Data FROM Schema1.tbl_Sample_Table)
END;
GO  
CREATE VIEW Schema2.vw_Test as 
SELECT * FROM Schema1.tbl_Sample_Table;
GO  
CREATE PROCEDURE Schema2.sp_Test @VAL CHAR(3) as 
SELECT * FROM Schema1.tbl_Sample_Table
WHERE Sample_Data = @VAL;
GO  
REVERT ;  
GO 

Everything should execute successfully.

Now will test restrictions for User2:

/* test Permission failures for User2*/
EXECUTE AS USER = 'User2' ;  
GO  
CREATE VIEW Schema1.vw_Failed_Test as 
SELECT * FROM Schema1.tbl_Sample_Table;
GO  
CREATE PROCEDURE dbo.sp_Failed_Test as 
SELECT * FROM Schema1.tbl_Sample_Table;
GO  
REVERT ;   

It should return following errors:
Msg 2760, Level 16, State 1, Procedure vw_Failed_Test, Line 1 [Batch Start Line 92]
The specified schema name "Schema1" either does not exist or you do not have permission to use it.
Msg 2760, Level 16, State 1, Procedure sp_Failed_Test, Line 1 [Batch Start Line 95]
The specified schema name "dbo" either does not exist or you do not have permission to use it.

Test permissions for User1:

 /* test Permissions for User1*/

EXECUTE AS USER = 'User1' ;  
GO  
INSERT INTO Schema1.tbl_Sample_Table VALUES ('XYZ');
GO
EXEC Schema2.sp_Test 'XYZ';
GO
UPDATE Schema1.tbl_Sample_Table 
SET Sample_Data = '123' 
WHERE Sample_Data = 'XYZ';
GO
SELECT * FROM Schema2.vw_Test;
GO
DELETE FROM Schema1.tbl_Sample_Table
WHERE Sample_Data = '123';
GO
SELECT Schema2.fn_Test() as Function_Result;
GO
REVERT ;  
GO

It should return no errors:

If we try to use User1 to create an object like this:

/* test Permission failures for User1*/
EXECUTE AS USER = 'User1' ;  
GO  
CREATE PROCEDURE Schema2.sp_Failed_Test as SELECT 1 as ABC;
GO
REVERT ;  
GO

Will get an error:
Msg 262, Level 14, State 18, Procedure sp_Failed_Test, Line 1 [Batch Start Line 130]
CREATE PROCEDURE permission denied in database 'TestPermissionDB'.

Do not forget to drop test database at the end:

/* Drop Test database */
use master;
GO
DROP DATABASE TestPermissionDB;
GO