How to ensure data is no longer on disk

I have a table with some sensitive customer data in it. I am now
keeping all the data in another table, and encrypting it. I want to
get rid of the original unencrypted data and be sure that it is no
longer anywhere on disk. Should I drop the table, or first delete the
rows and then do a dump tran? I'm not sure how to know if the data is
actually physically deleted from disk, or if it's still there, but
just in blocks that get marked as available. Any guidance would be
greatly appreciated.

Thanks,
Brucesandell@.pacbell.net (Bruce) wrote in message news:<595024a5.0405061512.13a1c5f1@.posting.google.com>...
> I have a table with some sensitive customer data in it. I am now
> keeping all the data in another table, and encrypting it. I want to
> get rid of the original unencrypted data and be sure that it is no
> longer anywhere on disk. Should I drop the table, or first delete the
> rows and then do a dump tran? I'm not sure how to know if the data is
> actually physically deleted from disk, or if it's still there, but
> just in blocks that get marked as available. Any guidance would be
> greatly appreciated.
> Thanks,
> Bruce

It depends how serious you are about getting rid of the unencrypted
data. You could add a new physical disk to the server, create a new
empty database on it, copy over all the data except the unencrypted
data, then remove the existing disk and format it and/or overwrite the
sectors with a suitable low-level disk tool.

If that is overkill for your needs, you could UPDATE all the
unencrypted data to something meaningless and then checkpoint the
database, which should overwrite the current pages on disk. You could
then DELETE the rows, and delete any transaction log backup files
(assuming you don't need them for recovery).

Don't forget that the unencrypted data may still exist in database
backups, so you would need to address that issue also.

Simon

How to encrypt Views?

Hi,

I found that I could encrypt StoredProcedures in Sql Server Express but have no idea about encrypting views. Any idea?

Use With Encryption Option while creating view. Once it is encrypted you will not be able to decrypt that... so keep a backup of the script

Eg.

create view vwTest

with encryption

as

select *from sys.objects

Madhu

|||

You can encrypt VIEW definitions, Stored Procedure code, and User Defined Function code.

As Madhu indicated, you should be sure to keep a copy of the VIEW definition, Stored Procedure or Function code because if you ever need to change the encryption object, you will not be able to do so without the original code. When you execute an ALTER statement, you are just replacing the original with a new copy of the code.

|||

You can neither create a View with with a query nor alter it. CREATE VIEW.... scripts are not available for SQL SERVER EXPRESS. You can use these scripts with Stored Procedures, but not with View. Your information about keeping a copy of the scripts which I encrypt was helpful, thanks.

|||

You can neither create a View with with a query nor alter it -- I am not able to understand what u want to mention. I would say.. you can create a view with query and you can alter it too...

scripts are not available for SQL SERVER EXPRESS -- If this is related to scripting a object in sql server express... it is very well available... you can script any object...

Madhu|||

Then create a sql server database file (.mdf) and try your script:

create view vwTest

with encryption

as

select *from sys.objects

If you can run this script tell me how you could because I could not find a way to do it.

|||

i have done this my self many times and i have tried now also... there is no issue.. post the error which u r getting...

Madhu

|||

Though you could send me the steps to make things easier but did not, I figured it out that I could replace the auto-generated select statement with a CREATE VIEW... script to create views but it executes with the below error:

"The CREATE VIEW SQL construct or statement is not supported."

|||

hi,

are you perhaps using the Compact edition of SQL Server 2005?

http://download.microsoft.com/download/A/4/7/A47B7B0E-976D-4F49-B15D-F02ADE638EBE/Compact_Express_Comparison.doc#_Toc149919962

this edition does not provide stored procedures, views and triggers support..

regards

|||

I'm not using compact edition. I'm using Sql Server Express Edition with Windows Forms provided in Visual Studio.

|||

hi,

Bluehunter wrote:

I'm not using compact edition. I'm using Sql Server Express Edition with Windows Forms provided in Visual Studio.

then stored procedure, views and trigger are supported for sure...

just in case, try executing via a command object the following statement against a datareader

SELECT @.@.VERSION; --<-- -- which should return - Microsoft SQL Server 2005 - 9.00.3152.00 (Intel X86) Mar 3 2007 03:17:37 Copyright (c) 1988-2005 Microsoft Corporation Express Edition on Windows NT 5.2 (Build 3790: Service Pack 2)

or similar ..

regards

How to Encrypt a SQL Server 2000 Database

Hello,
I am looking for some hint and help on how to go about encrypting a SQL Serv
er 2000 Database. So far I have looked into the possibility of encrypting da
ta in tables using my custom functions, however this process seems pretty sl
ow and resource intensive.
Ideally I would want a solution where in the whole DB can be encrypted so th
at I don't have to make any changes to my stored procedures, functions etc.
In case someone manages to login to EM or Query Analyzer, would he be able t
o access data or modify stored procedures in such a scenario. Also would he
be able to restore the DB from a backup copy and misuse it ?
Also what are the pros and cons of using EFS on Win 2k.
TIA."Kaustav" <Kaustav@.discussions.microsoft.com> wrote in message
news:E805034F-39E2-433C-A171-818B1D30C95C@.microsoft.com...
> I am looking for some hint and help on how to go about encrypting a SQL
Server 2000 Database. So far I have looked into the possibility of
encrypting data in tables using my custom functions, however this process
seems pretty slow and resource intensive. Ideally I would want a solution
where in the whole DB can be encrypted so that I don't have to make any
changes to my stored procedures, functions etc.
You can use EFS, just be sure to encrypt the mdf & ldf files under the
credentials (logged in as) the account running MSSQLServer service. Also,
you'd need to use the same account to decrypt the files.

> In case someone manages to login to EM or Query Analyzer, would he be able
to access data or modify stored procedures in such a scenario. Also would he
be able to restore the DB from a backup copy and misuse it ?
You can properly secure SQL Server, use Windows Authentication, so you can
"lock down" who gets and does not get access. Here's a starting point with
one good link to the security white paper:
http://www.microsoft.com/sql/techin...v
er.asp

> Also what are the pros and cons of using EFS on Win 2k.
Slight degradation in performance.
Steve