Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion Groups
DB Engine
SQL ServerMSDESQL Server CE
Services
Analysis (Data Mining)Analysis (OLAP)DTSIntegration ServicesNotification ServicesReporting Services
Programming
CLRConnectivitySQLXML
Other Technologies
ClusteringEnglish QueryFull-Text SearchReplicationService Broker
General
Data WarehousingPerformanceSecuritySetupSQL Server ToolsOther SQL Server Topics
DirectoryUser Groups
Related Topics
MS AccessOther DB ProductsMS Server Products.NET DevelopmentVB DevelopmentJava DevelopmentMore Topics ...

SQL Server Forum / General / Security / February 2008

Tip: Looking for answers? Try searching our database.

Can see deleted data in MDF & Backup

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Elmo - 13 Feb 2008 20:32 GMT
I have a customer who need to send us their database but wont do so because
of sensitive info contained in it.  They have tried deleting and truncatng
the data but you can still see the data if you open the mdf or backup with a
text editor?
Russell Fields - 13 Feb 2008 22:36 GMT
Elmo,

Have them use scripts (these can be generated from Enterprise Manager (2000)
or Managment Studio (2005)) to create another database.  Then use the data
import wizards to import the remaining data from their database into the new
database.  Only the data will come over, not the pages that still contain
undeleted fragments of sensitive data.

Then they can send you the new database.

RLF

>I have a customer who need to send us their database but wont do so because
> of sensitive info contained in it.  They have tried deleting and truncatng
> the data but you can still see the data if you open the mdf or backup with
> a
> text editor?
Elmo - 14 Feb 2008 08:46 GMT
Thanks Russell

Will this include all of the security and users/user groups and existing
data from the tables etc. Would they be able to do this using scripts?

> Elmo,
>
[quoted text clipped - 13 lines]
> > a
> > text editor?
Russell Fields - 14 Feb 2008 14:06 GMT
Elmo,

All of those things can be scripted, but they will need to be sure to
include all parts.  I don't know which version of SQL Server you are on, but
(although there are differences) you can script out from the GUI.

SQL Server 2000 is easier, since the script database has options to choose
all of these things.

SQL Server 2005 is not as encompassing a script.
1.  Roles and Users can be selected and scripted.
2.  There is no GUI tool to generate role memberships, but here is one
Erland Sommarskog wrote:

SELECT 'EXEC sp_addrolemember ''' + r.name + ''', ''' + u.name + ''''
FROM sys.database_role_members rm
JOIN sys.database_principals u
ON rm.member_principal_id = u.principal_id
JOIN sys.database_principals r
ON rm.role_principal_id = r.principal_id
WHERE u.name <> 'dbo'

3. The create database can be scripted.
4. All Objects (tables, views, stored procedures, etc, (except for encrypted
stored procedures.)) with permissions are scriptable.

Moving data from one database to another is not really scripted, but the
Import and Export wizards work fine, provided you are at an up-to-date
service pack for both the server and the tools.

Or there are tools, such as Red Gate SQL Compare, that can be instructed to
handle all of those issues.  (Compare original database to blank database
and get a script of everything that needs to be scripted into the new
database.)  I use Red Gate, but there are others and I really recommend that
a SQL Server shop have such tools on hand at all times.

Getting this done this way is obviously a lot more work than just getting a
backup, but it may reduce the concerns for your clients.

RLF

> Thanks Russell
>
[quoted text clipped - 24 lines]
>> > a
>> > text editor?
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2009 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.