Security

use master
go

---
--- Create new Login

drop login Gracie;
go

CREATE LOGIN Gracie with password='Goof'
go


---
--- Create new user, map to a login
---

use ReportBuilderTraining

CREATE USER Gracie FOR LOGIN Gracie;
go


---
--- Grant some permissions to nwe user
---

GRANT SELECT ON dbo.Address TO Gracie;  
GRANT SELECT ON dbo.Student TO Gracie;
go


---
--- Trim the columns the new user can view
---

REVOKE SELECT ON dbo.Student TO Gracie;
GRANT SELECT ON dbo.Student (StudentId, FirstName, LastName, Email) TO Gracie;  
go


---
--- "Run As" the new user to test their permissions
---

execute as user='Gracie'

SELECT SUSER_NAME() [Login], USER_NAME() [User] 
go

select * from Student
go

select FirstName from Student
go

revert

SELECT SUSER_NAME() [Login], USER_NAME() [User] 
go


---
--- Create a view to trim permissions, and grant permissions to it
---

REVOKE SELECT ON dbo.Student (StudentId, FirstName, LastName, Email) TO Gracie;  
go

CREATE VIEW dbo.vStudentPublic as 

	select StudentId, FirstName, LastName, Email
		from dbo.Student

GO

select * from dbo.vStudentPublic
go

GRANT SELECT ON dbo.vStudentPublic TO Gracie;  
go

---
--- Test if Gracie user has access to this view, even though she doesn't
--- have permissions to it's underlying tables
---

execute as user='Gracie'

SELECT SUSER_NAME() [Login], USER_NAME() [User] 
go

select * from dbo.vStudentPublic
go

revert

SELECT SUSER_NAME() [Login], USER_NAME() [User] 
go




---
--- explore metadata to check granted permissions
---

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

select b.class_desc, b.state_desc, b.permission_name, *
	from sys.database_principals a
	join sys.database_permissions b
	on a.principal_id=b.grantee_principal_id
	where a.name='Gracie'

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='Gracie'

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.views t
	on b.major_id=t.object_id
	where a.name='Gracie'