Sql-server – Copy encrypted database views

sql server

I need to take a copy of a SQL Server database. The database has approx 40 views that link to other databases, and I need sample data from those views too. Also, the views are encrypted so when I try and generate a script from one of the views this is the error:

Property TextHeader is not available for View '[dbo].[TABLE1]'. This property may not 
exist for this object, or may not be retrievable due to insufficient access rights.  The 
text is encrypted. (Microsoft.SqlServer.Smo)

What would be the best way to get a copy of this database containing data from the views? I was thinking about creating extra tables and populating with sample data but that seems long-winded, so is there a better way?

Best Answer

Encrypted stored procs and views can be decrypted pretty easily:

http://www.mssqltips.com/tip.asp?tip=1046

That'll give you the unencrypted text of the stored procedures and views. Just change the CREATE statement to be an ALTER, and it'll overwrite the encrypted versions with decrypted ones.

Don't do this in production - do it in a restored backup of the database. That way if it's a third party vendor app, you'll still be supported in production.

Related Solutions

Sql-server – Property PopulationStatus is not available for FullTextCatalog… How to fix

I found out that even if it is not installed it is still available on SQL Server Management Studio and you can create the indexes. It shouldn't allow...

Then I found this: SQL Server 2008 R2 Express - Installation Options, to allow FTS I need the 800MB version...

To check the status of FTS I found these queries:

SELECT
    FULLTEXTCATALOGPROPERTY(cat.name,'ItemCount'),
    FULLTEXTCATALOGPROPERTY(cat.name,'MergeStatus'),
    FULLTEXTCATALOGPROPERTY(cat.name,'PopulateCompletionAge'),
    FULLTEXTCATALOGPROPERTY(cat.name,'PopulateStatus'),
    FULLTEXTCATALOGPROPERTY(cat.name,'ImportStatus')
FROM sys.fulltext_catalogs AS cat

SELECT fulltextserviceproperty('IsFulltextInstalled')

Sql-server – Property Owner is not available for Database ‘DBNAME’

myUser is probably already a database user. You shoudl be passing a login principal to the stored procedure. Do something like this:

use DBNAME
go

exec sp_changedbowner 'myLoginThatShouldBeDBO'
go

Best Answer

Related Solutions

Sql-server – Property PopulationStatus is not available for FullTextCatalog… How to fix

Sql-server – Property Owner is not available for Database ‘DBNAME’

Related Question