Learn authorization roles and impersonate in sql server 

In this video, you will learn how to set user with certain permissions and than using impersonation to run override permission
Below is script used in this video:
--Authorization in sql server
--Techsapphire
--Email : contact@techsapphire.net
--Website: www.techsapphire.net
--Phone : +91-9360223756
CREATE LOGIN app_user
WITH PASSWORD = 'app_user@1234';
GO
CREATE USER app_user FOR LOGIN app_user;
GO
create role app_role;
grant EXEC, select, insert, update, delete on database::OrderApp to app_role;
ALTER ROLE app_role ADD MEMBER app_user;
select * from sys.database_role_members
CREATE TRIGGER sometrigger
ON [DineTable]
AFTER INSERT, DELETE, update
AS
print('abc')
CREATE LOGIN trigger_user
WITH PASSWORD = '5lap9d9P%Pg2@v*t12';
GO
CREATE USER trigger_user FOR LOGIN trigger_user;
GO
ALTER ROLE db_owner ADD MEMBER trigger_user;
GRANT IMPERSONATE ON USER::trigger_user TO app_user;
--execute as user ='trigger_user'
--DISABLE TRIGGER sometrigger ON dbo.[DineTable];
ALTER proc testprocedure
as
BEGIN
execute as user ='trigger_user';
DISABLE TRIGGER sometrigger ON dbo.[DineTable];
select SUSER_NAME() as something;
ENABLE TRIGGER sometrigger ON dbo.[DineTable];
revert
select SUSER_NAME() as something;
END
-- run on app_user
execute testprocedure
execute as user ='trigger_user';
select SUSER_NAME()
revert