USE master
GO

CREATE LOGIN TestLogin WITH PASSWORD = '<specifystrongpassword>', CHECK_POLICY = ON, CHECK_EXPIRATION = ON
GO

USE SQL2008SBSFS
GO

CREATE USER TestLogin FOR LOGIN TestLogin
GO

USE SQL2008SBS
GO

CREATE USER TestUser WITHOUT LOGIN
GO

CREATE ROLE SubmitOrdersRole AUTHORIZATION dbo
GO

CREATE PROCEDURE Customers.asp_submitorder @CustomerID  INT, @SKU  CHAR(10), @Quantity  INT
AS
DECLARE @OrderID    INT
BEGIN TRY
    BEGIN TRANSACTION
    
    INSERT INTO Orders.OrderHeader
    (CustomerID, OrderDate, SubTotal, TaxAmount, ShippingAmount)
    SELECT @CustomerID, GETDATE(), @Quantity * UnitPrice, 0, 5.00
    FROM Products.ProductOptions
    WHERE SKU = @SKU
    
    SET @OrderID = SCOPE_IDENTITY()
    
    INSERT INTO Orders.OrderDetail
    (OrderID, SKU, Quantity, UnitPrice)
    SELECT @OrderID, @SKU, @Quantity, UnitPrice
    FROM Products.ProductOptions
    WHERE SKU = @SKU    
END TRY

BEGIN CATCH
    ROLLBACK TRANSACTION
END CATCH
    COMMIT TRANSACTION
GO

--Test procedure
EXEC Customers.asp_submitorder 1, '1-2RB1-4RO', 5

SELECT OrderID, CustomerID, OrderDate, SubTotal, TaxAmount, ShippingAmount, GrandTotal, FinalShipDate
FROM Orders.OrderHeader
SELECT OrderDetailID, OrderID, SKU, Quantity, UnitPrice, ShipDate
FROM Orders.OrderDetail
GO

--Build proc to submit orders
GRANT EXECUTE ON Customers.asp_submitorder TO SubmitOrdersRole

--Impersonate TestUser and test the procedure again
SELECT USER_NAME()
GO
EXECUTE AS USER = 'TestUser'
GO
SELECT USER_NAME()
GO
EXEC Customers.asp_submitorder 1, '1-2RB1-4RO', 5
GO
SELECT OrderID, CustomerID, OrderDate, SubTotal, TaxAmount, ShippingAmount, GrandTotal, FinalShipDate
FROM Orders.OrderHeader
SELECT OrderDetailID, OrderID, SKU, Quantity, UnitPrice, ShipDate
FROM Orders.OrderDetail
GO
--Revert user context back to dbo
REVERT
GO

SELECT * FROM sys.objects
GO
EXECUTE AS USER = 'TestLogin'
GO
SELECT * FROM sys.objects
GO
SELECT OrderID, CustomerID, OrderDate, SubTotal, TaxAmount, ShippingAmount, GrandTotal, FinalShipDate
FROM Orders.OrderHeader
GO
REVERT
GO
GRANT SELECT ON DATABASE::SQL2008SBS TO TestLogin
GO
EXECUTE AS USER = 'TestLogin'
GO
SELECT * FROM sys.objects
GO
SELECT OrderID, CustomerID, OrderDate, SubTotal, TaxAmount, ShippingAmount, GrandTotal, FinalShipDate
FROM Orders.OrderHeader
GO
REVERT
GO
DENY VIEW DEFINITION ON DATABASE::SQL2008SBS TO TestLogin
GO
EXECUTE AS USER = 'TestLogin'
GO
SELECT * FROM sys.objects
GO
SELECT OrderID, CustomerID, OrderDate, SubTotal, TaxAmount, ShippingAmount, GrandTotal, FinalShipDate
FROM Orders.OrderHeader
GO
REVERT
GO