Make database to be read only

How can I make database to be read only?
Answer:
To make database to be read only, you can use the Enterprise Manager or the sp_dboption system stored procedure.For example, to make the pubs database to be read only, you can use the following statement:
Scripts:
EXEC sp_dboption 'pubs', 'read only', 'TRUE'
To make it online again, follow the below script
EXEC sp_dboption 'pubs', 'read only', 'FALSE'

Encryption and Decryption of SQL Server Data

Subject: Built in Encryption in SQL Server 2005:

In SQL Server 2005, T-SQL support for symmetric encryption and asymmetric encryption using keys, certificates and passwords. This article describes how to create, manage and use symmetric keys and certificates.

Because of the amount of information involved, I've divided this article into three sections:

• Part 1: Service and Master Keys
• Part 2: Certificates
• Part 3: Symmetric Keys

Service Master Key

Each SQL Server 2005 installation has exactly one Service Master Key (SMK), which is generated at install time. The SMK directly or indirectly secures all other keys on the server, making it the "mother of all SQL Server encryption keys." The Windows Data Protection API (DPAPI), at the higher O/S level, uses the SQL Server service account credentials to automatically encrypt and secure the SMK.

Because it is automatically created and managed by the server, Service Master Keys require only a few administrative tools. The SMK can be backed up via the BACKUP SERVICE MASTER KEY T-SQL statement. This statement has the following format:

BACKUP SERVICE MASTER KEY TO FILE = 'path_to_file'
ENCRYPTION BY PASSWORD = 'password'


Should you ever need to restore the Service Master Key from the backup copy, you can use the RESTORE SERVICE MASTER KEY statement:

RESTORE SERVICE MASTER KEY FROM FILE = 'path_to_file'
DECRYPTION BY PASSWORD = 'password' [FORCE]

Database Master Keys

While each SQL Server has a single Service Master Key, each SQL database can have its own Database Master Key (DMK). The DMK is created using the CREATE MASTER KEY statement:

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password'

This statement creates the DMK, encrypts it using the supplied password, and stores it in the database. In addition, the DMK is encrypted using the Service Master Key and stored in the master database; a feature known as "automatic key management." We'll talk more about this feature later.

Like the Service Master Key, you can backup and restore Database Master Keys. To backup a DMK, use the BACKUP MASTER KEY statement. The syntax is analogous to backing up a Service Master Key.

BACKUP MASTER KEY TO FILE = 'path_to_file'
ENCRYPTION BY PASSWORD = 'password'

Restoring the Database Master Key requires that you use the DECRYPTION BY PASSWORD clause, which specifies the password previously used to encrypt the backup file. In addition you must use the ENCRYPTION BY PASSWORD clause, which gives SQL Server a password to encrypt the DMK after it is loaded in the database.

RESTORE MASTER KEY FROM FILE = 'path_to_file'
DECRYPTION BY PASSWORD = 'password'
ENCRYPTION BY PASSWORD = 'password'
[ FORCE ]

To drop a DMK, use the DROP MASTER KEY statement:

DROP MASTER KEY

Part 2: Certificates
he following sample script creates a Database Master Key, a test certificate and demonstrates how to encrypt/decrypt
data using the certificate.

-- Sample T-SQL Script to demonstrate Certificate Encryption

-- Use the AdventureWorks database
USE AdventureWorks;

-- Create a Database Master Key
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'p@ssw0rd';

-- Create a Temp Table
CREATE TABLE Person.#Temp
(ContactID   INT PRIMARY KEY,
FirstName   NVARCHAR(200),
MiddleName  NVARCHAR(200),
LastName    NVARCHAR(200),
eFirstName  VARBINARY(200),
eMiddleName VARBINARY(200),
eLastName   VARBINARY(200));

-- Create a Test Certificate, encrypted by the DMK
CREATE CERTIFICATE TestCertificate
  WITH SUBJECT = 'Adventureworks Test Certificate',
  EXPIRY_DATE = '10/31/2009';

-- EncryptByCert demonstration encrypts 100 names from the Person.Contact table
INSERT
INTO Person.#Temp (ContactID, eFirstName, eMiddleName, eLastName)
SELECT ContactID, EncryptByCert(Cert_ID('TestCertificate'), FirstName),
 EncryptByCert(Cert_ID('TestCertificate'), MiddleName),
 EncryptByCert(Cert_ID('TestCertificate'), LastName)
FROM Person.Contact
WHERE ContactID <= 100;

-- DecryptByCert demonstration decrypts the previously encrypted data
UPDATE Person.#Temp
SET FirstName = DecryptByCert(Cert_ID('TestCertificate'), eFirstName),
 MiddleName = DecryptByCert(Cert_ID('TestCertificate'), eMiddleName),
 LastName = DecryptByCert(Cert_ID('TestCertificate'), eLastName);

-- View the results
SELECT *
FROM Person.#Temp;

-- Clean up work:  drop temp table, test certificate and master key
DROP TABLE Person.#Temp;
DROP CERTIFICATE TestCertificate;
DROP MASTER KEY;

Part 3: Symmetric Keys

Here is a sample T-SQL script demonstrating encryption and decryption by symmetric key:

-- Use the AdventureWorks database
USE AdventureWorks;

-- Create a Database Master Key
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'p@ssw0rd';

-- Create a Temp Table
CREATE TABLE Person.#Temp
(ContactID   INT PRIMARY KEY,
FirstName   NVARCHAR(200),
MiddleName  NVARCHAR(200),
LastName    NVARCHAR(200),
eFirstName  VARBINARY(200),
eMiddleName VARBINARY(200),
eLastName   VARBINARY(200));

-- Create a Test Certificate
CREATE CERTIFICATE TestCertificate
  WITH SUBJECT = 'Adventureworks Test Certificate',
  EXPIRY_DATE = '10/31/2009';

-- Create a Symmetric Key
CREATE SYMMETRIC KEY TestSymmetricKey
 WITH ALGORITHM = TRIPLE_DES
   ENCRYPTION BY CERTIFICATE TestCertificate;
OPEN SYMMETRIC KEY TestSymmetricKey
  DECRYPTION BY CERTIFICATE TestCertificate;

-- EncryptByKey demonstration encrypts 100 names from the Person.Contact table
INSERT
INTO Person.#Temp (ContactID, eFirstName, eMiddleName, eLastName)
SELECT ContactID,
 EncryptByKey(Key_GUID('TestSymmetricKey'), FirstName),
 EncryptByKey(Key_GUID('TestSymmetricKey'), MiddleName),
 EncryptByKey(Key_GUID('TestSymmetricKey'), LastName)
FROM Person.Contact
WHERE ContactID <= 100;

-- DecryptByKey demonstration decrypts the previously encrypted data
UPDATE Person.#Temp
SET FirstName = DecryptByKey(eFirstName),
 MiddleName = DecryptByKey(eMiddleName),
 LastName = DecryptByKey(eLastName);

-- View the results
SELECT *
FROM Person.#Temp;

-- Clean up work:  drop temp table, symmetric key, test certificate and master key
DROP TABLE Person.#Temp;
CLOSE SYMMETRIC KEY TestSymmetricKey;
DROP SYMMETRIC KEY TestSymmetricKey;
DROP CERTIFICATE TestCertificate;
DROP MASTER KEY;