--THIS DEMO SHOWCASES SOME OF THE NEW PERMISSION
--ENHANCEMENTS IN YUKON 

--SYSADMIN NO LONGER REQUIRED FOR PROFILING
--create a login for the account with auditing privileges
use master
CREATE LOGIN AUDIT_MAN WITH PASSWORD = 'A!234BCD'
--grant the permission to perform audits
GRANT ALTER TRACE TO AUDIT_MAN
--now login as AUDIT_MAN and try to run profiler

use exec_db
grant impersonate on user::truncator to tabowner
go
select user_name()
execute as user = 'tabowner'
select user_name()
execute as user = 'truncator'
select user_name()
revert
select user_name()
revert
select user_name()

execute as user = 'tabowner'
select has_perms_by_name('truncator','user','impersonate')
revert

--*****************************************************************************************************************
/*
--EXECUTE at the database level
use exec_context_db
--find out all existing permissions on all procs
--notice that the only permissions on the procs are 
--granted to test3
select 
	A.name, C.name as granteeName, 
	B.type as permission, B.state as perm_state
from 
	sys.objects as A, 
	sys.database_permissions as B, 
	sys.database_principals as C
where 
	A.type = 'P' and A.object_id = B.major_id 
	and B.grantee_principal_id = C.principal_id

--lets create a new vanilla user
CREATE LOGIN TEST44 WITH PASSWORD = 'A!234BCD'
CREATE USER TEST44 FOR LOGIN TEST44
--grant execute to this user...he will be able 
--to access these procs
--this is a database level grant
GRANT EXECUTE TO TEST44

--setuser to test4 and execute the proc
execute as user= 'TEST44'
exec PublicSchema.VendorAccessProc2 'CA'
revert
--Now you can also deny or grant at a per schema level
DENY EXECUTE ON SCHEMA::PublicSchema TO TEST44
Execute as user = 'TEST44'
exec PublicSchema.VendorAccessProc2 'IN'
revert

--New permission to allow for impersonating other users.
GRANT impersonate on user::test1 to test2
execute as user = 'test2'
--notice how test2 can impersonate test1 even though 
--test2 is not dbo or sysadmin
execute as user = 'test1'
select user_name()
revert
select user_name()
revert
select user_name()
*/