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 ; GOEverything 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 ; GOIt should return no errors:
/* test Permission failures for User1*/ EXECUTE AS USER = 'User1' ; GO CREATE PROCEDURE Schema2.sp_Failed_Test as SELECT 1 as ABC; GO REVERT ; GOWill 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
