Author: sqldba

To use encryption and later be able to decrypt data in a column using symmetric encryption involves the following:

1. Service Master Key – unique to each sql server instanced
2. Database Master Key – unique to each database, generated using password and service master key
3. One or more Symmetric Database Certificates
4. One or more Symmetric Encryption Keys that use an algorithm and are tied to a certificate

———————————————————-
— This only needs to be run once after database creation
———————————————————-
USE [database]
GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘MyEncryptionPassword’
GO

CREATE CERTIFICATE SSN_CERT_01 WITH SUBJECT = ‘SSN_CERT_01’
GO

CREATE SYMMETRIC KEY SSN_KEY_01
WITH ALGORITHM = AES_128
ENCRYPTION BY CERTIFICATE SSN_CERT_01
GO

GRANT CONTROL ON CERTIFICATE::[SSN_CERT_01] TO [APPLICATION_USER]
GO

GRANT CONTROL ON SYMMETRIC KEY::[SSN_KEY_01] TO [APPLICATION_USER]
GO

———————————————————-
— This is how to encrypt a value to be inserted into
— column of database table
— This needs to be encapsulated in stored procedures
— that are called from the application, so as not to
— expose encrypt/decrypt process within application code
— NOTE: Will need to ‘OPEN’ the key once per session
———————————————————-

OPEN SYMMETRIC KEY SSN_KEY_01

DECRYPTION BY CERTIFICATE SSN_CERT_01;

UPDATE t_person

SET ssn = EncryptByKey( Key_GUID(‘SSN_KEY_01’), @ssn )

WHERE person_id = @person_id

CLOSE SYMMETRIC KEY SSN_KEY_01

———————————————————-
— This is how to decrypt a value to select from
— column of database table
— This needs to be encapsulated in stored procedures
— that are called from the application, so as not to
— expose encrypt/decrypt process within application code
— NOTE: The cast needs to be the same datatype as the
— data was before it was encrypted
———————————————————-

OPEN SYMMETRIC KEY SSN_KEY_01

DECRYPTION BY CERTIFICATE SSN_CERT_01;

SELECT TOP 1 @ssn = cast( DecryptByKey(A.ssn) as nvarchar(10) )

FROM t_person A WITH (NOLOCK)

WHERE A.person_id = @person_id

CLOSE SYMMETRIC KEY SSN_KEY_01

MOVING FROM INSTANCE TO INSTANCE

By default, a database’s master key is encrypted using the server’s service master key. When you move a database to a new server using backup/restore or detach/attach, the database master key can not be decrypted automatically when you attempt to use the symmetric key. This is because the “service master key” is used to automatically open the database’s master key. At this point there are a couple of options in order to get encryption working again in the database:

1) Decode all data on source instance; create backup; restore backup; recreate master key, certificate, and encryption key, then re-encrypt the data… yeh, right.

2) Explicitly OPEN MASTER KEY every time you want to use it… this is problematic as it will require code changes.

3) BACKUP SERVICE MASTER KEY on the original instance and use RESTORE SERVICE MASTER KEY on the target server… This can be bad as it hoses up the MASTER KEYS in all the databases on the instance.

4) You can back up the certificates on the original database; create database backup; restore database on target server; drop the keys, certificates, and master key in restored database; recreate the master key; restore the certificate from the backup file; recreate the encryption key. Probably a good practice to backup the certificate, but still is a lot of work.

–TO BACK UP CERTIFICATE ON SOURCE INSTANCE
BACKUP CERTIFICATE SB_CERT
TO FILE = ‘C:SANDBOX_CERT.cer’
WITH PRIVATE KEY ( FILE = ‘C:SANDBOX_CERT_Key.pvk’
, ENCRYPTION BY PASSWORD = ‘SandBox_P@$$w0rd’ )

–ON TARGET INSTANCE AFTER DB RESTORE
–AND COPY CERT FILES TO TARGET’S FILE SYSTEM
CREATE CERTIFICATE SB_CERT
FROM FILE = ‘C:SANDBOX_CERT.cer’
WITH PRIVATE KEY ( FILE = ‘C:SANDBOX_CERT_Key.pvk’
, DECRYPTION BY PASSWORD = ‘SandBox_P@$$w0rd’ )

5) Easiest solution is use the following command on the restored database:

USE dbname
GO

OPEN MASTER KEY DECRIPTION BY PASSWORD = ‘MyEncryptionPassword’
GO

ALTER MASTER KEY REGENERATE WITH ENCRYPTION BY PASSWORD = ‘MyEncryptionPassword’
GO

NOTE: The master key password needs to match the password you used to create master key on original database. You also need CONTROL permission on database to fiddle with master key.

To create the linked server:

USE [msdb]
GO

EXEC sp_addlinkedserver @server = N’SAMPLE_LINK’, @srvproduct=N’Oracle’, @provider=N’OraOLEDB.Oracle’, @datasrc=N’LINKDSN’
GO

EXEC sp_addlinkedsrvlogin @rmtsrvname = ‘SAMPLE_LINK’, @rmtuser = ‘LINKUSER’, @rmtpassword = ‘*******’, @useself = False
GO

To drop the linked server:

USE [msdb]
GO

EXEC sp_droplinkedsrvlogin ‘SAMPLE_LINK’, NULL
GO

EXEC sp_dropserver ‘SAMPLE_LINK’
GO

A quick way to generate script to disable all FK constraints in current db:

SELECT ‘ALTER TABLE ‘ + B.name + ‘ NOCHECK CONSTRAINT ‘ + C.name
FROM sysconstraints A
, sysobjects B
, sysobjects C
WHERE A.id = B.id
AND A.constid = C.id
AND C.type = ‘F’

A quick way to generate script to enable all FK constraints in current db:

SELECT ‘ALTER TABLE ‘ + B.name + ‘ CHECK CONSTRAINT ‘ + C.name
FROM sysconstraints A
, sysobjects B
, sysobjects C
WHERE A.id = B.id
AND A.constid = C.id
AND C.type = ‘F’

This sql generates a grant blanket execute on stored procedures in a database:

GRANT EXECUTE TO {USER_NAME}
GO

This sql generates a grant execute statement for each stored procedure in the database:

SELECT ‘GRANT EXECUTE ON ‘ + name + ‘ TO {USER_NAME}’
FROM sysobjects
WHERE type IN ( ‘P’, ‘FN’ )
AND name NOT LIKE ‘dt_%’
ORDER BY name

This scripts grants execute to all stored procedures in the database:

USE {DATABASE_NAME}
GO

DECLARE @proc_name varchar(128)
DECLARE @sql nvarchar(255)
DECLARE proc_cur CURSOR FAST_FORWARD
FOR
SELECT name
FROM sysobjects WITH (NOLOCK)
WHERE type IN (‘P’,’FN’)
OPEN proc_cur
FETCH proc_cur INTO @proc_name
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = ‘grant execute on ‘ + @proc_name + ‘ to {USER_NAME}’
EXECUTE sp_executesql @sql
FETCH proc_cur INTO @proc_name
END
CLOSE proc_cur
DEALLOCATE proc_cur
GO

CREATE FUNCTION fn_Split

( @text varchar(8000)

, @delimiter varchar(20) = ' '

)
RETURNS @Strings TABLE
(    
  position int IDENTITY PRIMARY KEY
, value varchar(8000)   
)
AS
BEGIN

DECLARE @index int 

SET @index = -1 

WHILE (LEN(@text) > 0) 
 BEGIN  
   SET @index = CHARINDEX(@delimiter , @text)  
   IF (@index = 0) AND (LEN(@text) > 0)  
    BEGIN   
     INSERT INTO @Strings VALUES (@text)
         BREAK  
    END  
   IF (@index > 1)  
    BEGIN   
      INSERT INTO @Strings VALUES (LEFT(@text, @index - 1))   
      SET @text = RIGHT(@text, (LEN(@text) - @index))  
    END  
   ELSE 
    SET @text = RIGHT(@text, (LEN(@text) - @index)) 
 END
RETURN
END
GO

use sandbox1
go

— Script out indexes completely, including both PK’s and regular indexes, each clustered or nonclustered.
— DOES NOT HANDLE COMPRESSION; that’s ok, since 2008 R2 RTM benchmarking shows it’s faster and results in smaller indexes to insert uncompressed and then compress later
— HARDCODES [dbo] schema (i.e. it doesn’t say [JohnDoe].[table], changing that to [dbo].[table]
— modified from original version from http://www.sqlservercentral.com/Forums/Topic961088-2753-2.aspx

DECLARE
@idxTableName SYSNAME,
@idxTableID INT,
@idxname SYSNAME,
@idxid INT,
@colCount INT,
@IxColumn SYSNAME,
@IxFirstColumn BIT,
@ColumnIDInTable INT,
@ColumnIDInIndex INT,
@IsIncludedColumn INT,
@sIncludeCols VARCHAR(MAX),
@sIndexCols VARCHAR(MAX),
@sSQL VARCHAR(MAX),
@sParamSQL VARCHAR(MAX),
@sFilterSQL VARCHAR(MAX),
@location SYSNAME,
@IndexCount INT,
@CurrentIndex INT,
@CurrentCol INT,
@Name VARCHAR(128),
@IsPrimaryKey TINYINT,
@Fillfactor INT,
@FilterDefinition NVARCHAR(MAX),
@IsClustered BIT, — used solely for putting information into the result table
@PKDropSQL NVARCHAR(MAX)

IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE id = object_id(N'[tempdb].[dbo].[#IndexSQL]’))
DROP TABLE [dbo].[#IndexSQL]

CREATE TABLE #IndexSQL
( TableName VARCHAR(128) NOT NULL
,IndexName VARCHAR(128) NOT NULL
,IsClustered BIT NOT NULL
,IsPrimaryKey BIT NOT NULL
,IndexCreateSQL NVARCHAR(max) NOT NULL
,PKDropSQL NVARCHAR(max) NULL
)

IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE id = object_id(N'[tempdb].[dbo].[#IndexListing]’))
DROP TABLE [dbo].[#IndexListing]

CREATE TABLE #IndexListing
(
[IndexListingID] INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
[TableName] SYSNAME COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ObjectID] INT NOT NULL,
[IndexName] SYSNAME COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[IndexID] INT NOT NULL,
[IsPrimaryKey] TINYINT NOT NULL,
[FillFactor] INT,
[FilterDefinition] NVARCHAR(MAX) NULL
)

IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE id = object_id(N'[tempdb].[dbo].[#ColumnListing]’))
DROP TABLE [dbo].[#ColumnListing]

CREATE TABLE #ColumnListing
(
[ColumnListingID] INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
[ColumnIDInTable] INT NOT NULL,
[Name] SYSNAME COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ColumnIDInIndex] INT NOT NULL,
[IsIncludedColumn] BIT NULL
)

INSERT INTO #IndexListing( [TableName], [ObjectID], [IndexName], [IndexID], [IsPrimaryKey], [FILLFACTOR], [FilterDefinition] )
SELECT OBJECT_NAME(si.object_id), si.object_id, si.name, si.index_id, si.Is_Primary_Key, si.Fill_Factor, NULL –si.filter_definition –filter_definition is in sql 2008
FROM sys.indexes si
LEFT OUTER JOIN information_schema.table_constraints tc ON si.name = tc.constraint_name AND OBJECT_NAME(si.object_id) = tc.table_name
WHERE OBJECTPROPERTY(si.object_id, ‘IsUserTable’) = 1
ORDER BY OBJECT_NAME(si.object_id), si.index_id

SELECT @IndexCount = @@ROWCOUNT, @CurrentIndex = 1

WHILE @CurrentIndex 0
BEGIN

SELECT @CurrentCol = 1

SELECT @IxFirstColumn = 1, @sIncludeCols = ”, @sIndexCols = ”

WHILE @CurrentCol 0
BEGIN
SET @sIncludeCols = @sIncludeCols + ‘,’
END

SET @sIncludeCols = @sIncludeCols + ‘[‘ + @IxColumn + ‘]’

END

SET @CurrentCol = @CurrentCol + 1
END

TRUNCATE TABLE #ColumnListing

–append to the result
IF LEN(@sIncludeCols) > 0
SET @sIndexCols = @sSQL + @sIndexCols + CHAR(13) + ‘) ‘ + ‘ INCLUDE ( ‘ + @sIncludeCols + ‘ ) ‘
ELSE
SET @sIndexCols = @sSQL + @sIndexCols + CHAR(13) + ‘) ‘

— Add filtering
IF @FilterDefinition IS NOT NULL
SET @sFilterSQL = ‘ WHERE ‘ + @FilterDefinition + ‘ ‘ + CHAR(13)
ELSE
SET @sFilterSQL = ”

— Build the options
SET @sParamSQL = ‘WITH ( PAD_INDEX = ‘

IF INDEXPROPERTY(@idxTableID, @idxname, ‘IsPadIndex’) = 1
SET @sParamSQL = @sParamSQL + ‘ON,’
ELSE
SET @sParamSQL = @sParamSQL + ‘OFF,’

SET @sParamSQL = @sParamSQL + ‘ ALLOW_PAGE_LOCKS = ‘

IF INDEXPROPERTY(@idxTableID, @idxname, ‘IsPageLockDisallowed’) = 0
SET @sParamSQL = @sParamSQL + ‘ON,’
ELSE
SET @sParamSQL = @sParamSQL + ‘OFF,’

SET @sParamSQL = @sParamSQL + ‘ ALLOW_ROW_LOCKS = ‘

IF INDEXPROPERTY(@idxTableID, @idxname, ‘IsRowLockDisallowed’) = 0
SET @sParamSQL = @sParamSQL + ‘ON,’
ELSE
SET @sParamSQL = @sParamSQL + ‘OFF,’

SET @sParamSQL = @sParamSQL + ‘ STATISTICS_NORECOMPUTE = ‘

— THIS DOES NOT WORK PROPERLY; IsStatistics only says what generated the last set, not what it was set to do.
IF (INDEXPROPERTY(@idxTableID, @idxname, ‘IsStatistics’) = 1)
SET @sParamSQL = @sParamSQL + ‘ON’
ELSE
SET @sParamSQL = @sParamSQL + ‘OFF’

— Fillfactor 0 is actually not a valid percentage on SQL 2008 R2
IF ISNULL( @FillFactor, 90 ) 0
SET @sParamSQL = @sParamSQL + ‘ ,FILLFACTOR = ‘ + CAST( ISNULL( @FillFactor, 90 ) AS VARCHAR(3) )

IF (@IsPrimaryKey = 1) — DROP_EXISTING isn’t valid for PK’s
BEGIN
SET @sParamSQL = @sParamSQL + ‘ ) ‘
SET @PKDropSQL = ‘ALTER TABLE [‘ + @idxTableName + ‘] DROP CONSTRAINT [‘ + @idxName + ‘]’
END
ELSE
BEGIN
SET @sParamSQL = @sParamSQL + ‘ ,DROP_EXISTING = ON ) ‘
SET @PKDropSQL = NULL
END

SET @sSQL = @sIndexCols + CHAR(13) + @sFilterSQL + CHAR(13) + @sParamSQL

— 2008 R2 allows ON [filegroup] for primary keys as well, negating the old “IF THE INDEX IS NOT A PRIMARY KEY – ADD THIS – ELSE DO NOT” IsPrimaryKey IF statement
SET @sSQL = @sSQL + ‘ ON [‘ + @location + ‘]’

–PRINT @sIndexCols + CHAR(13)
INSERT INTO #IndexSQL (TableName, IndexName, IsClustered, IsPrimaryKey, IndexCreateSQL, PKDropSQL)
VALUES (@idxTableName, @idxName, @IsClustered, @IsPrimaryKey, @sSQL, @PKDropSQL)

END

SET @CurrentIndex = @CurrentIndex + 1
END

SELECT * FROM #IndexSQL

USE [dbname]
GO

—————————————-
— Drop all constraints (PKs & FKs)
—————————————-
SET NOCOUNT ON
DECLARE @id int
, @objname nvarchar(256)
, @constr_name nvarchar(128)
, @count int
, @sql nvarchar(2000)
DECLARE @objs AS TABLE ( id int identity(1,1), objname nvarchar(256), objtype varchar(128))
INSERT INTO @objs ( objname, objtype )
SELECT table_schema + ‘.’ + table_name
, constraint_name
FROM information_schema.table_constraints
ORDER BY table_name
, constraint_type
SELECT @id = max(id) FROM @objs
SET @count = @id
WHILE @id > 0
BEGIN
SELECT @objname = objname
, @constr_name = objtype
FROM @objs WHERE id = @id
SET @sql = ‘ALTER TABLE ‘ + @objname + ‘ DROP CONSTRAINT [‘ + @constr_name + ‘]’
PRINT @sql
–EXECUTE sp_executesql @sql
SET @id = @id – 1
SET @count = @count + 1
END
PRINT CAST(@count as varchar(10)) + ‘ Constraint(s) deleted’
GO

—————————————-
— Drop all Indexes
—————————————-
SET NOCOUNT ON
DECLARE @id int
, @tabname nvarchar(128)
, @idxname nvarchar(128)
, @ispk tinyint
, @count int
, @sql nvarchar(2000)
DECLARE @objs AS TABLE ( id int identity(1,1), tabname nvarchar(128), idxname varchar(128), ispk tinyint )
INSERT INTO @objs ( tabname, idxname, ispk )
SELECT OBJECT_NAME(si.object_id) “tabname”
, si.name “idxname”
, si.Is_Primary_Key “ispk”
FROM sys.indexes si
LEFT OUTER JOIN information_schema.table_constraints tc
ON si.name = tc.constraint_name AND OBJECT_NAME(si.object_id) = tc.table_name
WHERE OBJECTPROPERTY(si.object_id, ‘IsUserTable’) = 1
AND si.name IS NOT NULL
ORDER BY 1, 2
SELECT @id = max(id) FROM @objs
WHILE @id > 0
BEGIN
SELECT @tabname = tabname
, @idxname = idxname
, @ispk = ispk
FROM @objs
WHERE id = @id
SET @sql = CASE @ispk when 1 then ‘ALTER TABLE [‘ + @tabname + ‘] DROP CONSTRAINT [‘ + @idxname + ‘]’
else ‘DROP INDEX [‘ + @idxname + ‘] ON [‘ + @tabname + ‘] WITH ( ONLINE = OFF )’ END
PRINT @sql
SET @id = @id – 1
END
GO

This is a replacement for the undocumented sp_MSforeachdb system stored proc.

USE [master];
GO

CREATE PROCEDURE dbo.sp_foreachdb
@command NVARCHAR(MAX),
@replace_character NCHAR(1) = N’?’,
@print_dbname BIT = 0,
@print_command_only BIT = 0,
@suppress_quotename BIT = 0,
@system_only BIT = NULL,
@user_only BIT = NULL,
@name_pattern NVARCHAR(300) = N’%’,
@database_list NVARCHAR(MAX) = NULL,
@recovery_model_desc NVARCHAR(120) = NULL,
@compatibility_level TINYINT = NULL,
@state_desc NVARCHAR(120) = N’ONLINE’,
@is_read_only BIT = 0,
@is_auto_close_on BIT = NULL,
@is_auto_shrink_on BIT = NULL,
@is_broker_enabled BIT = NULL
AS
BEGIN
SET NOCOUNT ON;

DECLARE
@sql NVARCHAR(MAX),
@dblist NVARCHAR(MAX),
@db NVARCHAR(300),
@i INT;

IF @database_list > N”
BEGIN
;WITH n(n) AS
(
SELECT ROW_NUMBER() OVER (ORDER BY s1.name) – 1
FROM sys.objects AS s1
CROSS JOIN sys.objects AS s2
)
SELECT @dblist = REPLACE(REPLACE(REPLACE(x,”,’,’),
”,”),”,”)
FROM
(
SELECT DISTINCT x = ‘N”’ + LTRIM(RTRIM(SUBSTRING(
@database_list, n,
CHARINDEX(‘,’, @database_list + ‘,’, n) – n))) + ””
FROM n WHERE n

use master
go

CREATE PROCEDURE sp_GetAllTableSizes
AS
DECLARE @tabname varchar(128)

–Cursor to get the name of all user tables from the sysobjects listing
DECLARE table_cur CURSOR FAST_FORWARD FOR
SELECT [name]
FROM dbo.sysobjects (NOLOCK)
WHERE OBJECTPROPERTY(id, N’IsUserTable’) = 1

–A procedure level temp table to store the results
CREATE TABLE #TempTable
(
tableName varchar(128),
numberofRows int,
reservedSize varchar(50),
dataSize varchar(50),
indexSize varchar(50),
unusedSize varchar(50)
)

–Open the cursor
OPEN table_cur

–Get the first table name from the cursor
FETCH NEXT FROM table_cur INTO @tabname

–Loop until the cursor was not able to fetch
WHILE (@@Fetch_Status >= 0)
BEGIN
–Dump the results of the sp_spaceused query to the temp table
INSERT #TempTable
EXEC sp_spaceused @tabname

–Get the next table name
FETCH NEXT FROM table_cur INTO @tabname
END

–Get rid of the cursor
CLOSE table_cur
DEALLOCATE table_cur

–Select all records so we can use the reults
SELECT *
FROM #TempTable
ORDER BY numberofRows DESC

–Final cleanup!
DROP TABLE #TempTable

GO