execute as

CREATE PROCEDURE dbo.usp_Demo WITH EXECUTE AS ‘SqlUser1’ AS SELECT user_name(); — Shows execution context is set to SqlUser1. EXECUTE AS CALLER; SELECT user_name(); — Shows execution context is set to SqlUser2, the caller of the module. REVERT; SELECT user_name(); — Shows execution context is set…

grant vs revoke vs deny

https://www.mssqltips.com/sqlservertip/2894/understanding-grant-deny-and-revoke-in-sql-server/ Grant – gives perms GRANT SELECT ON OBJECT::Test.TestTable TO TestRole; Revoke- undoes perms whether it’s grant or deny REVOKE SELECT ON OBJECT::Test.TestTable FROM TestRole; Deny – blocks access and trumps all other access DENY SELECTONOBJECT::Test.TestTable TO TestUser;   — Query sys.database_permissions to see applicable permissions…