/************************************
Chapter 9 Scripts
For MCITP SQL Server 2005 Database Developer All-in-One Exam Guide
By Darril Gibson

The scripts available here are the same ones used in each of the chapters. Ideally, you would type in the script and learn from any errors you might make. Occasionally, however, you may want a copy of the script to paste into the query window or into a batch file. If so, feel free to use these script files. 

I strongly encourage you not to rely on these pre-typed scripts, but instead to get used to typing the T-SQL yourself. The more you do it (even if you’re just typing what you see in the book), the more comfortable you’ll get with T-SQL.

Some scripts in the book are intended to be run from the command line. They've been included where appropriate so that you can copy and paste them into Notepad and save them as a batch file (.bat). You can then run the batch file from the command prompt.

Be aware that some of the scripts fail on purpose. For example, the book may be showing how a script works, and then after a constraint is added, the book may show how the same script fails.  The failure is not an error. In other words, just running the scripts without the benefit of the book won't be much help.

*************************************/

SELECT dbo.fn_CountLogins(@UserName) AS 'Self';

/************************************/

SELECT * from sp_helpdb --  will result in error
SELECT sp_helpdb as 'HelpDb'	-- will result in error

/************************************/

Exec sp_helpdb;

/************************************/

sp_server_info

/************************************/

USE AdventureWorks;
sp_server_info;

/************************************/

USE AdventureWorks;
EXEC sp_server_info;

/************************************/

Exec sp_helpdb;

/************************************/

Exec sp_helpdb AdventureWorks;

/************************************/

sp_helptext sp_updatestats

/************************************/

USE AdventureWorks;
GO
BEGIN TRANSACTION
  UPDATE Person.Contact
  SET LastName = 'Cain'
  WHERE ContactID = 2
  -- data not committed yet
COMMIT TRANSACTION

/************************************/

SELECT * FROM
Production.ProductInventory
WHERE ProductID = 1

/************************************/

USE AdventureWorks;
GO
BEGIN TRANSACTION
	UPDATE Production.ProductInventory
		SET Quantity = Quantity - 10
		WHERE ProductID = 1 AND LocationID=1
	UPDATE Production.ProductInventory
		SET Quantity = Quantity + 10
		WHERE ProductID = 1 AND LocationID=6
COMMIT TRANSACTION

/************************************/

SELECT * FROM
Production.ProductInventory
WHERE ProductID = 1

/************************************/

USE AdventureWorks;
GO
BEGIN TRAN
  INSERT INTO Person.Contact
	(FirstName, LastName,PasswordHash, PasswordSalt)
	VALUES ('Harry', 'Potter', 'P@ssw0rd','P@$$S@lt')
  -- This leaves an open transaction that can be closed with
  -- ROLLBACK TRAN

/************************************/

BEGIN TRAN
  INSERT INTO Person.Contact
   (FirstName, LastName, PasswordHash, PasswordSalt)
  VALUES ('Harry', 'Potter', 'P@ssw0rd', 'P@$$s@lt')
  DBCC OPENTRAN

/************************************/

Exec sp_who 52

/************************************/

USE AdventureWorks;
GO 
ROLLBACK TRAN;
DBCC OPENTRAN

/************************************/

USE AdventureWorks
BEGIN TRANSACTION
	UPDATE Production.ProductInventory
		SET Quantity = Quantity - 10
		WHERE ProductID = 1 AND LocationID=1
	UPDATE Production.ProductInventory
		SET Quantity = Quantity + 10
		WHERE ProductID = 1 AND LocationID=6
COMMIT TRANSACTION

/************************************/

BEGIN TRANSACTION
	UPDATE Production.ProductInventory
		SET Quantity = Quantity - 1000
	WHERE ProductID = 1 AND LocationID=1
	If (SELECT Quantity
		FROM Production.ProductInventory
		WHERE ProductID = 1 and LocationID = 1) < 0
	BEGIN
		ROLLBACK TRANSACTION
		RETURN
	END
	UPDATE Production.ProductInventory
		SET Quantity = Quantity + 1000
		WHERE ProductID = 1 AND LocationID = 6
COMMIT TRANSACTION

/************************************/

USE AdventureWorks;
GO
SELECT ProductID, Name, ListPrice
FROM Production.Product
WHERE ProductID = 514;

/************************************/

BEGIN TRAN
  USE AdventureWorks;
  GO
  UPDATE Production.Product
  SET ListPrice = ListPrice * 1.1
  WHERE ProductID=514
  SELECT ProductID, Name, ListPrice
  FROM Production.Product
  WHERE ProductID = 514

/************************************/

--Window 2
USE AdventureWorks;
GO
SELECT ProductID, Name, ListPrice
FROM Production.Product
WHERE ProductID = 514;

/************************************/

ROLLBACK TRAN;
SELECT ProductID, Name, ListPrice
FROM Production.Product
WHERE ProductID = 514;

/************************************/

BEGIN TRAN
  UPDATE Production.Product
  SET ListPrice = ListPrice * 1.1
  WHERE ProductID=514
  IF (SELECT ListPrice 
    FROM Production.Product 
    WHERE ProductID=514) > $1000
  BEGIN
    PRINT 'Price is > $1000.  This UPDATE needs special approval'
    ROLLBACK TRAN
    RETURN
  END
  SELECT ProductID, Name, ListPrice
  FROM Production.Product
  WHERE ProductID = 514
COMMIT TRAN

/************************************/

--Window 2
SELECT ProductID, Name, ListPrice
FROM Production.Product
WHERE ProductID = 514;

/************************************/

BEGIN TRAN
  UPDATE Production.Product
  SET ListPrice = ListPrice * 1.1
  WHERE ProductID=514
  IF (SELECT ListPrice 
    FROM Production.Product 
    WHERE ProductID=514) > $100
  BEGIN
    PRINT 'Price is > $100.  This UPDATE needs special approval'
    ROLLBACK TRAN
    RETURN
  END
  SELECT ProductID, Name, ListPrice
  FROM Production.Product
  WHERE ProductID = 514
COMMIT TRAN

/************************************/

UPDATE Production.Product
SET ListPrice = 133.24
Where ProductID=514
SELECT ProductID, Name, ListPrice
FROM Production.Product
WHERE ProductID = 514

/************************************/

USE AdventureWorks;
GO
SELECT * FROM Production.ProductInventory 
WHERE ProductID = 1;

/************************************/

BEGIN TRANSACTION
	UPDATE Production.ProductInventory
	SET Quantity = Quantity + 1000
	WHERE ProductID = 1 AND LocationID = 6
--We want the previous UPDATE to complete, but we want the ability to 
--rollback the following transaction.  The savepoint gives us that ability.
	SAVE TRANSACTION NegativeQtyCheck;
	UPDATE Production.ProductInventory
	SET Quantity = Quantity - 1000
	WHERE ProductID = 1 AND LocationID = 1
--Here we check for a negative condition and rollback to the savepoint 
--if the negative condition exists
	If (SELECT Quantity
		FROM Production.ProductInventory
		WHERE ProductID = 1 and LocationID = 1) < 0
	ROLLBACK TRANSACTION NegativeQtyCheck
--Now we review the results 
SELECT * FROM Production.ProductInventory 
WHERE ProductID = 1;

/************************************/

DBCC OPENTRAN

/************************************/

ROLLBACK TRAN
SELECT * FROM Production.ProductInventory 
	WHERE ProductID = 1	

/************************************/

BEGIN TRANSACTION
	UPDATE Production.ProductInventory
	SET Quantity = Quantity + 1000
	WHERE ProductID = 1 AND LocationID = 6
--We want the previous UPDATE to complete, but we want the ability to –
--rollback the following transaction.  The savepoint gives us that ability.
	SAVE TRANSACTION InventoryMove;
	UPDATE Production.ProductInventory
	SET Quantity = Quantity – 1000
	WHERE ProductID = 1 AND LocationID = 1
--Here we check for a negative condition and rollback to the savepoint 
--if the negative condition exists
	If (SELECT Quantity
		FROM Production.ProductInventory
		WHERE ProductID = 1 and LocationID = 1) < 0
	ROLLBACK TRANSACTION InventoryMove  -- rollback to savepoint
--Now COMMIT the transaction. Even if the second UPDATE is rolled back
--the first UPDATE succeeds and is committed.  
COMMIT TRANSACTION

/************************************/

DBCC OPENTRAN

/************************************/

SELECT * FROM Production.ProductInventory 
	WHERE ProductID = 1

/************************************/

UPDATE Production.ProductInventory
SET Quantity = 324
WHERE ProductID = 1 AND LocationID = 6

/************************************/

BEGIN TRANSACTION TranName WITH MARK 'ArchiveOrders'

/************************************/

USE StockPortfolio;
GO
BEGIN TRAN
--Create a temporary table to hold the result of the INSERT
DECLARE @VerifyStocks table( 
	CustomerID int,
      StockName varchar(4),	--Unique stock code
      Date datetime,		--Date of order
      Qty int,			--Quantity of shares ordered
      Price);             	--Purchase price of stock
--Insert the order from the Shopping cart
 --Include the OUTPUT clause in the INSERT statement
INSERT INTO StockPurchaseOrder
   OUTPUT INSERT CustomerID, StockName, Date, Qty, Price INTO @VerifyStocks
       SELECT CustomerID, StockName, Date, Qty, Price
       FROM ShoppingCart;
--Count the number of rows in the temporary table and make a decision
IF (SELECT COUNT (*) FROM @VerifyStocks) > 5
   ROLLBACK TRAN
Else
   COMMIT TRAN

/************************************/

ALTER TABLE Production.ProductInventory
ADD CONSTRAINT CK_Quantity
CHECK (Quantity >= 0)

/************************************/

UPDATE Production.ProductInventory
SET Quantity = -1000
WHERE ProductID = 1 AND LocationID = 1

/************************************/

BEGIN TRY
  BEGIN TRANSACTION
	UPDATE Production.ProductInventory
		SET Quantity = Quantity -  1000
	WHERE ProductID = 1 AND LocationID = 1
	UPDATE Production.ProductInventory
		SET Quantity = Quantity + 1000
		WHERE ProductID = 1 AND LocationID = 6
  COMMIT TRANSACTION
END TRY
BEGIN CATCH
  ROLLBACK TRANSACTION
	PRINT 'Transaction rolled back'
END CATCH

/************************************/

SELECT * FROM Production.ProductInventory
WHERE ProductID = 1

/************************************/

DELETE FROM HumanResources.Employee
     WHERE EmployeeID = 1;
GO
PRINT 'Error Number: ' + CAST(@@ERROR AS VARCHAR(8));

/************************************/

SELECT * FROM HumanResources.Employee
   WHERE EmployeeID = 1;
GO
PRINT 'Error Number: ' + CAST(@@ERROR AS VARCHAR(8));

/************************************/

DELETE FROM HumanResources.Employee
 WHERE EmployeeID = 1
GO
SELECT * FROM HumanResources.Employee;
GO
IF @@Error <> 0 -- we have an error
  PRINT 'Error Number: ' + CAST(@@ERROR AS VARCHAR(8));
ELSE -- value = 0
  PRINT 'Successfully deleted record'	-- erroneous logic

/************************************/

DECLARE @ProductID int, @ListPrice money
SET @ProductID = 1
SET @ListPrice = -1.00
UPDATE Production.Product
SET ListPrice = @ListPrice
WHERE ProductID = @ProductID

/************************************/

DECLARE @ProductID int, @ListPrice money
SET @ProductID = 1
SET @ListPrice = -1.00
BEGIN TRY
  BEGIN TRANSACTION
     UPDATE Production.Product
     SET ListPrice = @ListPrice
     WHERE ProductID = @ProductID
  COMMIT TRANSACTION
END TRY
BEGIN CATCH
	If @ListPrice < 0 
		PRINT 'List Price cannot be a negative number.'
  ROLLBACK TRANSACTION
END CATCH

/************************************/

SELECT *
FROM Production.Product
WHERE ProductID = 1

/************************************/

SET @ListPrice = 1.23

/************************************/

SELECT *
FROM Production.Product
WHERE ProductID = 1

/************************************/

SELECT * FROM sys.messages
WHERE message_ID > 50000

/************************************/

USE master
GO
EXEC sp_addmessage 50001, 16, 
   'Uh Oh.  Something bad happened.';

/************************************/

RAISERROR (error number (or ‘text’), severity, state)

/************************************/

RAISERROR(50001, 16, 1)

/************************************/

RAISERROR (50001, 16, 1) WITH LOG

/************************************/

RAISERROR (50001, 16, 9) WITH LOG

/************************************/

RAISERROR ('Uh Oh.  Something bad happened.', 16, 1) 

/************************************/

USE AdventureWorks;
GO
SELECT pc.FirstName, pc.LastName, pc.EmailAddress
FROM Person.Contact pc
INNER JOIN HumanResources.Employee emp
ON pc.ContactID = emp.ContactID;

/************************************/

CREATE PROC usp_EmployeeEmailList
AS
SELECT pc.FirstName, pc.LastName, pc.EmailAddress
FROM Person.Contact pc
INNER JOIN HumanResources.Employee emp
ON pc.ContactID = emp.ContactID

/************************************/

EXEC usp_EmployeeEmailList

/************************************/

USE AdventureWorks; 
GO 
CREATE Proc dbo.usp_ViewRows 
  (@NumberOfRows int)
AS
SELECT TOP (@NumberOfRows) *
FROM Person.Contact

/************************************/

EXEC usp_ViewRows 5

/************************************/

ALTER Proc dbo.usp_ViewRows 
  (@NumberOfRows int = 5)
AS
SELECT TOP (@NumberOfRows) *
FROM Person.Contact

/************************************/

EXEC usp_ViewRows 
EXEC usp_ViewRows 10

/************************************/

ALTER PROC usp_EmployeeEmailList
 (@LastName varchar(50), @FirstName varchar(50))
AS
SELECT pc.FirstName, pc.LastName, pc.EmailAddress
FROM Person.Contact pc
INNER JOIN HumanResources.Employee emp
ON pc.ContactID = emp.ContactID
WHERE FirstName = @FirstName AND LastName = @LastName

/************************************/

EXEC usp_EmployeeEmailList  'Gilbert', 'Guy'

/************************************/

EXEC usp_EmployeeEmailList  

/************************************/

ALTER PROC usp_EmployeeEmailList
 (@LastName varchar(50) = NULL, @FirstName varchar(50) = NULL)
AS
IF (@LastName IS NULL and @FirstName IS NULL)
  SELECT pc.FirstName, pc.LastName, pc.EmailAddress
  FROM Person.Contact pc
  INNER JOIN HumanResources.Employee emp
  ON pc.ContactID = emp.ContactID
ELSE
  SELECT pc.FirstName, pc.LastName, pc.EmailAddress
  FROM Person.Contact pc
  INNER JOIN HumanResources.Employee emp
  ON pc.ContactID = emp.ContactID
  WHERE FirstName = @FirstName AND LastName = @LastName

/************************************/

EXEC usp_EmployeeEmailList  'Gilbert', 'Guy'
EXEC usp_EmployeeEmailList  

/************************************/

DECLARE @CustomerID int
SELECT @CustomerID = CustomerID 
FROM Orders WHERE OrderID = 54

/************************************/

DECLARE @myVar varchar(30)
SET @myVar = 'Hello World'

/************************************/

DECLARE @str varchar(30)
SET @str = 'Hello'
Print @str + ' Before GO'
GO
Print @str + ' After GO'

/************************************/

ALTER PROC usp_EmployeeEmailList
 (@LastName varchar(50) = NULL, @FirstName varchar(50) = NULL)
WITH ENCRYPTION
AS
IF (@LastName IS NULL and @FirstName IS NULL)
  SELECT pc.FirstName, pc.LastName, pc.EmailAddress
  FROM Person.Contact pc
  INNER JOIN HumanResources.Employee emp
  ON pc.ContactID = emp.ContactID
ELSE
  SELECT pc.FirstName, pc.LastName, pc.EmailAddress
  FROM Person.Contact pc
  INNER JOIN HumanResources.Employee emp
  ON pc.ContactID = emp.ContactID
  WHERE FirstName = @FirstName AND LastName = @LastName

/************************************/

EXEC sp_helptext usp_EmployeeEmailList

/************************************/

USE AdventureWorks;
GO
SELECT DATEADD(day, 21, OrderDate)AS TimeFrame 
FROM Sales.SalesOrderHeader;

/************************************/

SELECT OrderDate, DATEADD(day, 21, OrderDate)AS TimeFrame 
FROM Sales.SalesOrderHeader;

/************************************/

SELECT DATEADD(day, 21, '11/03/2008');

SELECT DATEADD(day, 21, '11/03/2008');

/************************************/

USE AdventureWorks;
GO
CREATE Proc dbo.usp_AddDaysToOrder
AS
SELECT OrderDate, DATEADD(day, 21, OrderDate)AS TimeFrame 
FROM Sales.SalesOrderHeader;
GO
CREATE Proc dbo.usp_AddDaysToDate
AS
SELECT DATEADD(day, 21, '11/03/2007');

/************************************/

EXEC dbo.usp_AddDaysToOrder;
EXEC dbo.usp_AddDaysToDate;

/************************************/

USE AdventureWorks;
GO
--This will add a variable number of days to the OrderDate
ALTER Proc dbo.usp_AddDaysToOrder
	@InputDays integer
AS
SELECT OrderDate, DATEADD(day, @InputDays, OrderDate)AS TimeFrame 
FROM Sales.SalesOrderHeader;
GO
--This will add 21 days to a given date
ALTER Proc dbo.usp_AddDaysToDate
	@InputDate datetime
AS
SELECT DATEADD(day, 21, @InputDate);

/************************************/

EXEC usp_AddDaysToOrder 12;
EXEC usp_AddDaysToDate '12/03/2008';

/************************************/

USE AdventureWorks;
GO
--This will add a variable number of days to a given date
ALTER Proc dbo.usp_AddDaysToDate
      @InputNumberofDays int,	@InputDate datetime
AS
SELECT DATEADD(day, @InputNumberofDays, @InputDate);

/************************************/

EXEC usp_AddDaysToDate 30,'11/03/2008';

/************************************/

EXEC usp_AddDaysToDate '11/03/2006', 30;

/************************************/

EXEC usp_AddDaysToDate @InputDate = '11/03/2007', @InputNumberofDays = 30;

/************************************/

USE AdventureWorks;
GO
DBCC CHECKDB;

/************************************/

Use Master;
GO
CREATE DATABASE Dba;

/************************************/

SELECT MAX(dbid) FROM master.dbo.sysdatabases;

/************************************/

--Declare variables
DECLARE @dbid integer;			--Current database
DECLARE @DBName nvarchar(50);		--Database name
DECLARE @mySQL nvarchar(200);		--SQL Statement
--Start with first database
SET @dbid = 1;
--Loop through all databases
WHILE @dbid < (SELECT MAX(dbid) FROM master.dbo.sysdatabases)
BEGIN
 	SELECT @DBName = name
 	  FROM master.dbo.sysdatabases
 	  WHERE dbid = @dbid;
 	--Dynamically build statement to execute for each database
 	SET @mySQL = 'DBCC CHECKDB(' + @DBName + ')'; 
 	--Show dynamically built SQL statement
 	SELECT 'Statement = ' + @mySQL AS 'Dynamically Built T-SQL Statement';
      --Increment database to do the same for the next database
      Set @dbid = @dbid + 1;
END;

/************************************/

	SELECT 'Statement = ' + @mySQL AS 'Dynamically Built T-SQL Statement';
      EXEC sp_executesql @statement = @mySQL

/************************************/

--SELECT 'Statement = ' + @mySQL AS 'Dynamically Built T-SQL Statement';

/************************************/

USE Dba;
GO
CREATE PROC usp_DBCC_CheckAllDB
AS
--Declare variables
DECLARE @dbid integer;
DECLARE @DBName nvarchar(50);
DECLARE @mySQL nvarchar(200);
--Start with first database
SET @dbid = 1;
--Loop through all databases
WHILE @dbid < (SELECT MAX(dbid) FROM master.dbo.sysdatabases)
BEGIN
 	SELECT @DBName = name
 	  FROM master.dbo.sysdatabases
 	  WHERE dbid = @dbid;
 	--Dynamically build statement to execute for each database
 	SET @mySQL = 'DBCC CHECKDB(' + @DBName + ')';
 --SELECT 'Statement = ' + @mySQL AS 'Dynamically Built T-SQL Statement';
      EXEC sp_executesql @statement = @mySQL
      --Increment database to do the same for the next database
      Set @dbid = @dbid + 1;
END;

/************************************/

USE dba;
GO
EXEC usp_DBCC_CheckAllDB

/************************************/

EXEC sp_recompile 'usp_ShowOrders'

/************************************/

CREATE PROCEDURE dbo.usp_ShowOrders @CustomerID int
WITH RECOMPILE
AS
    SELECT *
    FROM Orders
    WHERE CustomerID = @CustomerID;

/************************************/

CREATE PROCEDURE dbo.usp_ShowOrders2 @CustomerID int
WITH RECOMPILE
AS
    SELECT *
    FROM Orders
    WHERE CustomerID = @CustomerID;
--Only the following query will be recompiled each time the 
--stored procedure is executed
    UPDATE CustomerQuery
    SET QueryCount = QueryCount + 1
    WHERE CustomerID = @CustomerID
    OPTION (RECOMPILE);

/************************************/

EXEC usp_ShowOrders 101 WITH RECOMPILE

/************************************/

USE Master;
GO
CREATE DATABASE Chapter9

/************************************/

USE Chapter9;
GO
CREATE TABLE Gryffindor (
	StudentID int IDENTITY(100,1) NOT NULL, 
	LastName varchar(35) NULL, 
	FirstName varchar(35) NULL, 
	Points int NULL,
    	CONSTRAINT [PK_Gryffindor_StudentID] PRIMARY KEY CLUSTERED 
	(StudentID)
)

/************************************/

INSERT into Gryffindor VALUES('Longbottom','Neville',NULL)
INSERT into Gryffindor VALUES ('Thomas','Dean', NULL)
INSERT into Gryffindor VALUES ('Parvati','Patil', NULL)

/************************************/

CREATE LOGIN Dumbledore 
   WITH PASSWORD = 'P@ssw0rd';
USE Chapter9;
CREATE USER Dumbledore;

/************************************/

CREATE PROC uspStudentList
AS
   SELECT * FROM Gryffindor;
GO
Exec uspStudentList;

/************************************/

GRANT EXECUTE ON uspStudentList to Dumbledore

/************************************/

EXECUTE AS LOGIN = 'Dumbledore';
   SELECT * FROM Gryffindor;
REVERT;

/************************************/

EXECUTE AS LOGIN = 'Dumbledore';
EXEC uspStudentList;
REVERT;

/************************************/

CREATE PROC uspInsertStudent
   (@LastName varchar(35),@FirstName varchar(35))
AS
   INSERT INTO Gryffindor
   VALUES(@LastName,@FirstName, NULL);
GO
Exec uspInsertStudent 'Finnegan', 'Seamus'
EXEC uspStudentList;

/************************************/

CREATE PROC uspAssignPoints (@LastName varchar(35),@points int)
AS
UPDATE Gryffindor
   SET Points = @Points 
   WHERE LastName = @LastName
GO
Exec uspAssignPoints 'Longbottom', 5;
Exec uspStudentList;

/************************************/

CREATE PROC uspDeleteStudent (@LastName varchar(35))
AS
DELETE FROM Gryffindor
   WHERE @LastName = LastName;
GO
EXEC uspDeleteStudent ' Finnegan '
EXEC uspStudentList

/************************************/

EXECUTE AS LOGIN = 'Dumbledore';
INSERT INTO Gryffindor
   VALUES('Snape', 'Severus', NULL);
REVERT;

/************************************/

EXECUTE AS LOGIN = 'Dumbledore';
UPDATE Gryffindor
   SET Points = 10
   WHERE LastName = 'Longbottom';
REVERT;

/************************************/

EXECUTE AS LOGIN = 'Dumbledore';
DELETE FROM Gryffindor
   WHERE LastName = 'LongBottom';
REVERT;

/************************************/

GRANT EXECUTE ON uspInsertStudent to Dumbledore;
GRANT EXECUTE ON uspAssignPoints to Dumbledore;
GRANT EXECUTE ON uspDeleteStudent to Dumbledore;

/************************************/

EXECUTE AS LOGIN = 'Dumbledore';
EXEC uspInsertStudent 'Brown', 'Lavender';
EXEC uspStudentList;
REVERT;

/************************************/

EXECUTE AS LOGIN = 'Dumbledore';
EXEC uspAssignPoints 'Longbottom', 5;
EXEC uspStudentList;
REVERT;

/************************************/

EXECUTE AS LOGIN = 'Dumbledore';
EXEC uspDeleteStudent 'Longbottom';
EXEC uspStudentList;
REVERT;

/************************************/

DENY DELETE ON Gryffindor to Dumbledore
DENY INSERT ON Gryffindor to Dumbledore
DENY UPDATE ON Gryffindor to Dumbledore
DENY SELECT ON Gryffindor to Dumbledore

/************************************/

EXECUTE AS LOGIN = 'Dumbledore';
EXEC uspInsertStudent 'Brown', 'Lavender';
EXEC uspDeleteStudent 'Thomas';
EXEC uspAssignPoints 'Finnegan', 15
EXEC uspStudentList;
REVERT;

/************************************/

SELECT * 
FROM Users 
WHERE login = ‘txtLogin.Text’ and password = ‘txtPassword.Text’;

/************************************/

SELECT * 
FROM Users 
WHERE login = ‘darril’ and password = ‘P@ssw0rd’;

/************************************/

SELECT * 
FROM Users 
WHERE login = ‘darril’ or 1=1;
--and password = ‘P@ssw0rd’;

/************************************/

SELECT * 
FROM Users 
WHERE Login = ‘darril’ OR 1=1;
Drop table Users;
--AND Password = ‘P@ssw0rd’;

/************************************/

SELECT * 
FROM Users 
WHERE login = txtLogin.Text and password = txtPassword.Text;

/************************************/

USE AdventureWorks;
GO
CREATE Proc dbo.usp_SQLInjection
      @login nvarchar(20),
	@password nvarchar(20)
AS
SELECT * 
FROM Users
WHERE login = @login AND password = @password;

/************************************/

SELECT * 
FROM Users
WHERE login = [Darril OR 1=1’; --]
AND password = [P@ssw0rd]

/************************************/

CREATE Proc dbo.usp_SQLInjection2
      @login nvarchar(20),
	@password nvarchar(20)
AS
DECLARE @mySQL nvarchar(1000)
SET @mySQL = 'SELECT * FROM Users '
SET @mySQL = @mySQL + 'WHERE login = ' + @login 
SET @mySQL = @mySQL + 'AND password = ' + @password + ';'
EXEC sp_executesql @statement = @mySQL

/************************************/

CREATE Proc dbo.usp_ViewTable  
  (@TableName varchar (50)) 
AS 
SELECT *  
FROM @Tablename

/************************************/

USE AdventureWorks; 
GO 
CREATE Proc dbo.usp_ViewTable 
  (@NumberOfRows int, @TableName varchar (25))
AS
--Before we do anything, validate the input 
--Check to see if table exists
IF EXISTS 
 ( SELECT * FROM sys.tables
   WHERE NAME = @TableName )
BEGIN		--If table exists we go on, if not we print an error
--Get the schema so we can build the two-part table name
  DECLARE @SchemaID int
  SET @SchemaID = (
    SELECT Schema_ID FROM sys.tables
    WHERE Name = @TableName    )
--Build two-part table name
  DECLARE @Table varchar (100)
  Set @Table = SCHEMA_NAME(@SchemaID) + '.' + @TableName
--Build dynamic SELECT statement
  DECLARE @DynamicSQLString nvarchar(200)
  SET @DynamicSQLString = 
    'SELECT TOP ' + + CONVERT(varchar(2),@NumberOfRows) + ' * FROM ' + @Table + ''
--Execute the dynamically built SQL string
  Execute sp_executesql @DynamicSQLString
END 
ELSE --Validation of input failed
PRINT 'Table ' + @TableName + ' doesn''t exist'

/************************************/

Exec usp_ViewTable 5, Contact

/************************************/

Exec usp_ViewTable 5, Contacts

/************************************/

Imports System
Imports System.Data
Imports Microsoft.SqlServer.Server
Imports System.Data.SqlTypes
Public Class CLRClass
    <Microsoft.SqlServer.Server.SqlProcedure()> _
    Public Shared Sub HappyDays()
        Dim strWeekDay As String
        strWeekDay = WeekdayName(Weekday(Today()))
        SqlContext.Pipe.Send("Happy " & strWeekDay & "!")
    End Sub
End Class

/************************************/

cd c:\WINDOWS\Microsoft.NET\Framework\v2.0.50727

/************************************/

vbc /target:library c:\MCITPSuccess\happydays_dll.vb

/************************************/

USE Master;
GO
CREATE Database Chapter9

/************************************/

USE Chapter9;
GO
CREATE ASSEMBLY MCITP 
FROM 'c:\MCITPSuccess\Happy_dll.dll' 
WITH PERMISSION_SET = SAFE

/************************************/

ALTER DATABASE Chapter9
    SET Trustworthy ON

/************************************/

USE Chapter9;
GO
CREATE PROCEDURE uspSuccess
AS
EXTERNAL NAME 
        CLR_MCITPSuccess.CLRClass.HappyDays;

/************************************/

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'clr enabled', 1;
GO
RECONFIGURE;

/************************************/

Exec uspSuccess

/************************************/