sample for "execute as"

use analysisreporting
go
-- current user
SELECT SUSER_NAME(), USER_NAME();
select top 10 * from audit
go
-- deny select for a user
DENY SELECT ON [dbo].[Audit] TO [reportuser]
GO
-- change execute as
EXECUTE AS USER = 'reportuser';
SELECT SUSER_NAME(), USER_NAME();
-- this will fail
select top 10 * from audit
REVERT;
Go
-- revoke select for a user
REVOKE SELECT ON [dbo].[Audit] TO [reportuser]
GO
EXECUTE AS USER = 'reportuser';
SELECT SUSER_NAME(), USER_NAME();
-- this will run
select top 10 * from audit
REVERT;
Go
-- this is logged in user again
select top 10 * from audit
SELECT SUSER_NAME(), USER_NAME();
go