Roles

use ReportBuilderTraining
go

/*

---
--- Cleanup from past runs
---

EXEC sp_droprolemember 'ALL_ACCESS', 'Gracie'  
EXEC sp_droprolemember 'StudentPublic', 'Gracie'  

drop role all_access
drop role StudentPublic
drop role StudentPrivate

*/

---
--- Create new roles
---

CREATE ROLE ALL_ACCESS
CREATE ROLE StudentPublic
CREATE ROLE StudentPrivate
go

---
--- Grant permissions to tables for these roles
---

GRANT SELECT ON dbo.Major TO ALL_ACCESS;  
GRANT SELECT ON dbo.MajorProgram TO ALL_ACCESS;  
GRANT SELECT ON dbo.Department TO ALL_ACCESS;  
go

GRANT SELECT ON dbo.Address TO StudentPublic;  
GRANT SELECT ON dbo.Phone TO StudentPublic;  
GRANT SELECT ON dbo.StudentMajor TO StudentPublic; 
GRANT SELECT ON dbo.vStudentPublic TO StudentPublic; 
go

GRANT SELECT ON dbo.Student TO StudentPrivate;  
GRANT SELECT ON dbo.StudentHistory TO StudentPrivate;  
go


--- 
--- Give Gracie StudentPublic access, note that does not get her ALL_ACCESS rights
---

EXEC sp_addrolemember 'StudentPublic', 'Gracie'  
go


---
--- We can nest roles by placing roles in other roles. This will give anyone with
--- StudentPublic rights ALL_ACCESS rights as well. This is very similar to nesting
--- AD groups
---

EXEC sp_addrolemember 'ALL_ACCESS', 'StudentPublic'  
go

EXEC sp_addrolemember 'StudentPublic', 'StudentPrivate'  
EXEC sp_addrolemember 'ALL_ACCESS', 'StudentPrivate'  
go




---
--- explore metadata to check granted permissions and view role membership
---

select * from sys.database_principals
select * from sys.database_permissions
select * from sys.database_role_members

select b.class_desc, b.state_desc, b.permission_name, t.name, *
	from sys.database_principals a
	join sys.database_permissions b
	on a.principal_id=b.grantee_principal_id
	join sys.tables t
	on b.major_id=t.object_id
	where a.name='StudentPrivate'

select a.name, c.name
	from sys.database_principals a
	join sys.database_role_members b
	on a.principal_id=b.member_principal_id
	join sys.database_principals c
	on b.role_principal_id=c.principal_id