/************************************
Chapter 8 Scripts
For MCITP SQL Server 2005 Database Administration 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.

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

CREATE LOGIN Sally_TSQL 
    WITH PASSWORD = 'P@ssw0rd';
USE AdventureWorks;
CREATE USER Sally_TSQL_dbUser
    FOR LOGIN Sally_TSQL;

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

REVOKE CONNECT TO GUEST; 

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

Use Master;
Go
SELECT * FROM sys.server_principals

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

USE AdventureWorks;
GO
SELECT * FROM sys.database_principals

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

USE AdventureWorks;
GO
SELECT * FROM sys.database_permissions

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

USE AdventureWorks;
GO
SELECT * FROM sys.database_principals

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

USE AdventureWorks;
GO
SELECT * FROM sys.database_permissions

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

SELECT * FROM sys.database_permissions
WHERE grantee_principal_id = 5

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

USE AdventureWorks;
GO
SELECT * FROM sys.database_permissions

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

USE AdventureWorks;
SELECT * FROM sys.database_principals

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

USE Master;
GO
CREATE DATABASE Chapter8;
GO
USE Chapter8;
Go
CREATE TABLE dbo.Customer
(
	CustomerID int IDENTITY(1,1) NOT NULL, 
	LastName varchar(50) NOT NULL,
	FirstName varchar(50) NOT NULL, 
	CreditCardNumber nvarchar(20) NULL, 
	EncryptedCreditCardNumber varbinary(128) NULL,
    	CONSTRAINT [PK_Customer_CustomerID] PRIMARY KEY CLUSTERED 
	(
		CustomerID
	 )
);

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

INSERT INTO Customer VALUES('Jetson','George','1234-5678-1234',NULL);
INSERT INTO Customer VALUES('Jetson','Elroy','9876-5432-9876',NULL);
INSERT INTO Customer VALUES('Simpson','Homer','1029-3847-5765',NULL);
INSERT INTO Customer VALUES('Simpson','Bart','5678-1234-5678',NULL);
SELECT * FROM Customer;

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

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'P@ssw0rd'

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

CREATE CERTIFICATE CreditCardCert 
   WITH SUBJECT = 'Customer Credit Card Numbers'

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

CREATE SYMMETRIC KEY CreditCardKey1 WITH ALGORITHM = DES
    ENCRYPTION BY CERTIFICATE CreditCardCert;

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

OPEN SYMMETRIC KEY CreditCardKey1
   DECRYPTION BY CERTIFICATE CreditCardCert;

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

UPDATE Customer
SET EncryptedCreditCardNumber = 
		EncryptByKey(Key_GUID('CreditCardKey1'),CreditCardNumber)
SELECT * FROM Customer;

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

CLOSE SYMMETRIC KEY CreditCardKey1

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

OPEN SYMMETRIC KEY CreditCardKey1
   DECRYPTION BY CERTIFICATE CreditCardCert;

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

SELECT CustomerID, LastName,FirstName, CreditCardNumber    
   , CONVERT(nvarchar, DecryptByKey(EncryptedCreditCardNumber)) 
   AS 'Decrypted Credit Card Number'
    FROM Customer

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

CLOSE SYMMETRIC KEY CreditCardKey1

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

SELECT CustomerID, LastName,FirstName, CreditCardNumber, 
   CONVERT(nvarchar, DecryptByKeyAutoCert(cert_ID('CreditCardCert'),
	NULL, EncryptedCreditCardNumber)) 
   AS 'Decrypted Credit Card Number'
    FROM Customer

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

BACKUP SERVICE MASTER KEY TO FILE = 
  'c:\svcMasterKey.bak' ENCRYPTION BY PASSWORD = 'P@ssw0rd'

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

BACKUP MASTER KEY TO FILE =
	'c:\dbMasterKey.bak' ENCRYPTION BY PASSWORD ='P@ssw0rd'

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

BACKUP CERTIFICATE CreditCardCert TO FILE = 'c:\CreditCardCert.bak'
    WITH PRIVATE KEY ( FILE = 'c:\CreditCardKey1' , 
    ENCRYPTION BY PASSWORD = 'Pa$$w0rd' );