Sql-server – Securely remove columns (and traces thereof) from a SQL database

Securitysql serversql-server-2008

I've come upon a database that has previously held sensitive data (think "passwords/credit-card-numbers" kind of sensitive) in plain text. All of the sensitive data in question has been deleted, but I'm concerned that if accessed by someone with malicious intentions, the data might be recoverable through backups and/or transaction logs.

Is there a way to permanently and securely delete certain columns out of transaction logs and/or backup files while maintaining the integrity of all other data in a SQL database?

Best Answer

Well there is always a chance that someone can find the bits at various hardware layers if they had access to the physical disk(s), but I think you can be fairly confident you have removed traces of the data accessible through less nefarious means by:

rebuilding the table where the column was removed (perhaps perform a select into, drop the old table, rename the new one, and re-create any indexes)
starting the log chain over (a new full backup)
forcing the log to wrap around and re-zero-initialize by backing the log up twice and then performing a slight shrinkfile
eradicating any old full/diff/log backups (you may want to wait on this step until your data recovery window has passed, e.g. if you might still need to recover back to last Monday, wait until two Mondays from now - in the meantime moving them to a more secure location). At some point you know that a backup from two weeks ago is absolutely useless, because you're not going to restore to a point in time that far back, so why keep it around?

Related Solutions

Sql-server – securely exposing sql server 2008 for windows authentication

The words "securely", "exposing", "outside" and "database" do not belong together.

Under no circumstance should you ever expose a SQL server to the internet. It's a very bad idea.

So your question really should be: How do we give access to our SQL server to remote locations?

The answer: VPN. The remote users should establish a VPN connection to your network. From there you can give them the appropriate access to the database.

Sql-server – SQL Server, remove all users’ access right to a table object

This code should work below:

declare 
    @user_name sysname,
    @deny_cmd nvarchar(500)

declare UserCursor cursor for
select name
from sys.database_principals
where type in ('U', 'S')
and name not in ('dbo', 'information_schema', 'sys')

open UserCursor

fetch next from UserCursor
into @user_name

while @@fetch_status = 0
begin
    set @deny_cmd = 
        'deny select, insert, update, delete on object::UberSecureTable
        to ' + @user_name

    exec (@deny_cmd)

    fetch next from UserCursor
    into @user_name
end

close UserCursor
deallocate UserCursor

Let me know if that does what you're looking for. Change UberSecureTable to your actual table name.

Best Answer

Related Solutions

Sql-server – securely exposing sql server 2008 for windows authentication

Sql-server – SQL Server, remove all users’ access right to a table object

Related Question