/************************************ Chapter 8 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. *************************************/ CREATE LOGIN Sally WITH PASSWORD = 'password', CHECK_POLICY = OFF /************************************/ CREATE LOGIN TSQL_LoginWeakPW WITH PASSWORD = 'password', CHECK_POLICY = OFF /************************************/ CREATE LOGIN TSQL_LoginStrongPW2 WITH PASSWORD = 'P@ssw0rd' /************************************/ SELECT SUSER_SNAME(); /************************************/ EXECUTE AS LOGIN = 'yourComputerName\Nimfa'; SELECT SUSER_SNAME(); IF IS_SRVROLEMEMBER ('sysadmin') = 1 Print 'You are in the sysadmin role' REVERT GO EXECUTE AS LOGIN = 'yourComputerName\Darril'; SELECT SUSER_SNAME(); IF IS_SRVROLEMEMBER ('sysadmin') = 1 Print 'You are in the sysadmin role' /************************************/ IF IS_SRVROLEMEMBER ('sysadmin') = 1 Print 'You are in the sysadmin role' /************************************/ USE AdventureWorks; GO EXEC uspGetEmployeeManagers 1; /************************************/ SELECT * FROM Person.Contact WHERE LastName = 'Gilbert' /************************************/ USE AdventureWorks; GO EXEC uspGetEmployeeManagers 1; /************************************/ SELECT * FROM Person.Contact WHERE LastName = 'Gilbert' /************************************/ CREATE LOGIN Sally_TSQL WITH PASSWORD = 'P@ssw0rd '; USE AdventureWorks; CREATE USER Sally_TSQL_dbUser FOR LOGIN Sally_TSQL; /************************************/ -- Create the user in the database CREATE USER Steve WITHOUT Login -- Grant the user permissions on a table GRANT SELECT ON Person.Contact TO Steve --Use the Execute As clause to execute in the context of a uaser EXECUTE AS USER = 'Steve' SELECT * FROM Person.Contact REVERT /************************************/ USE database; GO GRANT CONNECT TO GUEST; /************************************/ REVOKE CONNECT TO GUEST; /************************************/ EXECUTE AS USER = 'Sally_dbUser' SELECT * FROM Person.Contact REVERT /************************************/ EXECUTE AS USER = 'Sally_dbUser' SELECT * FROM Person.Contact REVERT /************************************/ EXECUTE AS USER = 'Sally_dbUser' EXEC dbo.uspGetEmployeeManagers 1 REVERT /************************************/ EXECUTE AS USER = 'Sally_dbUser' EXEC dbo.uspGetEmployeeManagers 1 REVERT /************************************/ EXECUTE AS USER = 'Sally_dbUser' SELECT * FROM Production.Product SELECT * FROM Production.ProductInventory REVERT /************************************/ CREATE VIEW vwLowInventoryProduct AS SELECT p.ProductID, p.Name, p.ProductNumber, p.ReorderPoint, pi.Quantity FROM Production.Product AS P INNER JOIN Production.ProductInventory AS pi ON p.ProductID = pi.ProductID WHERE p.ReorderPoint > pi.Quantity /************************************/ GRANT SELECT ON vwLowInventoryProduct TO Sally_dbUser /************************************/ EXECUTE AS USER = 'Sally_dbUser' SELECT * FROM vwLowInventoryProduct REVERT /************************************/ USE database; GO CREATE ASSEMBLY assemblyName FROM 'path to dll file' WITH PERMISSION_SET = SAFE /************************************/ ALTER DATABASE databaseName SET Trustworthy ON /************************************/ ALTER DATABASE AdventureWorks SET DB_CHAINING ON /************************************/ -- List all users and roles in a database SELECT * FROM Sys.database_principals -- List all users and roles in a server SELECT * FROM Sys.server_principals -- List all SQL server logins SELECT * FROM Sys.sql_logins /************************************/ EncryptByKey(Key_GUID(' keyName '), ' Data to encrypt ') /************************************/ 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' ); /************************************/ /************************************/ /************************************/ /************************************/ /************************************/ /************************************/ /************************************/ /************************************/ /************************************/ /************************************/ /************************************/ /************************************/ /************************************/ /************************************/ /************************************/ /************************************/ /************************************/ /************************************/ /************************************/ /************************************/ /************************************/ /************************************/ /************************************/ /************************************/ /************************************/ /************************************/ /************************************/ /************************************/ /************************************/ /************************************/ /************************************/ /************************************/ /************************************/ /************************************/ /************************************/ /************************************/ /************************************/ /************************************/ /************************************/ /************************************/ /************************************/ /************************************/ /************************************/ /************************************/ /************************************/ /************************************/ /************************************/ /************************************/ /************************************/ /************************************/ /************************************/ /************************************/ /************************************/ /************************************/ /************************************/ /************************************/ /************************************/