--Before we start add the following users to your computer

--manager01
--manager02
--staff01
--staff02
--staff03

--Add a group called Managers to your computer
--Add the users manager01 & manager02 to this group

USE bookshopdb

--Configure group authentication - add logins to SQL Server
EXEC sp_grantlogin @loginame = 'BUILTIN\users'
EXEC sp_grantlogin @loginame = 'LAPTOP\managers'

sp_addlogin @loginame = 'devuser', @passwd = 'devpass'

sp_helplogins

--Authorise security accounts in BookShopDB - add users to BookshopDB
EXEC sp_grantdbaccess @loginame = 'BUILTIN\users',
@name_in_db = 'All Staff'
EXEC sp_grantdbaccess @loginame = 'LAPTOP\managers'
EXEC sp_grantdbaccess @loginame = 'devuser'
EXEC sp_grantdbaccess @loginame = 'LAPTOP\manager01'
EXEC sp_grantdbaccess @loginame = 'LAPTOP\staff01'
EXEC sp_grantdbaccess @loginame = 'LAPTOP\staff02'

--List user who can access BookShopDB
sp_helpuser

--Configure permisions
--List permissions assigned to Public role 
sp_helprotect @name = NULL, @username = 'public'

--Grant permissions to Public role
GRANT SELECT ON authors TO public
GRANT SELECT ON bookauthors TO public
GRANT SELECT ON bookcondition TO public
GRANT SELECT ON books TO public
GRANT SELECT ON customers TO public
GRANT SELECT ON formofpayment TO public
GRANT SELECT, INSERT, UPDATE, DELETE
ON orders TO public
GRANT SELECT, INSERT, UPDATE, DELETE
ON bookorders TO public
GRANT SELECT, INSERT, UPDATE, DELETE
ON orderstatus TO public

sp_helprotect

--Grant permissions to Managers group
GRANT INSERT, UPDATE, DELETE
ON authors TO [LAPTOP\managers]
GRANT INSERT, UPDATE, DELETE
ON bookauthors TO [LAPTOP\managers]
GRANT INSERT, UPDATE, DELETE
ON bookcondition TO [LAPTOP\managers]
GRANT INSERT, UPDATE, DELETE
ON books TO [LAPTOP\managers]
GRANT INSERT, UPDATE, DELETE
ON customers TO [LAPTOP\managers]
GRANT INSERT, UPDATE, DELETE
ON formofpayment TO [LAPTOP\managers]
GRANT ALL ON employees TO [LAPTOP\managers]
GRANT ALL ON positions to [LAPTOP\managers]

--Add users to fixed roles for BookShopDB
EXEC sp_addrolemember @rolename = 'db_backupoperator',
@membername = 'LAPTOP\staff01'

EXEC sp_addrolemember @rolename = 'db_backupoperator',
@membername = 'LAPTOP\staff02'

EXEC sp_addsrvrolemember @loginame = 'LAPTOP\managers',
@rolename = 'securityadmin'

EXEC sp_addsrvrolemember @loginame = 'LAPTOP\manager01',
@rolename = 'SysAdmin'

EXEC sp_addsrvrolemember @loginame = 'devuser',
@rolename = 'SysAdmin'