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 Dynamically Pull Stored Procedure Arguments

I'm sure there is a way using the system tables or maybe on the stored
procedures of the master db to pull the arguments for a given stored
procedure, and their datatypes.
So, for a stored proc like this:
CREATE StoredProc1
OrderNumber AS BIGINT,
OrderName AS VARCHAR(50)
AS ...
I would, using this magical query I am hoping exists, get back:
OrderNumber, BIGINT, 4
OrderName, VARCHAR, 50
or something in that order. Anyone have any ideas?Try,
use northwind
go
exec sp_procedure_params_rowset 'SalesByCategory'
go
AMB
"David Samson" wrote:

> I'm sure there is a way using the system tables or maybe on the stored
> procedures of the master db to pull the arguments for a given stored
> procedure, and their datatypes.
> So, for a stored proc like this:
> CREATE StoredProc1
> OrderNumber AS BIGINT,
> OrderName AS VARCHAR(50)
> AS ...
> I would, using this magical query I am hoping exists, get back:
> OrderNumber, BIGINT, 4
> OrderName, VARCHAR, 50
> or something in that order. Anyone have any ideas?|||From MS SQL Books On Line, you'll read about querying INFORMATION_SCHEMA.PAR
AMETERS:
"Contains one row for each parameter of a user-defined function or stored pr
ocedure accessible to the current user in the current
database. For functions, this view also returns one row with return value in
formation.
The INFORMATION_SCHEMA.PARAMETERS view is based on the sysobjects and syscol
umns system tables.
To retrieve information from these views, specify the fully qualified name o
f INFORMATION_SCHEMA view_name."
"David Samson" <CaptainSlock@.nospam.nospam> wrote in message news:54296B5C-F33F-426A-95A3-7
7366FF14200@.microsoft.com...
> I'm sure there is a way using the system tables or maybe on the stored
> procedures of the master db to pull the arguments for a given stored
> procedure, and their datatypes.
> So, for a stored proc like this:
> CREATE StoredProc1
> OrderNumber AS BIGINT,
> OrderName AS VARCHAR(50)
> AS ...
> I would, using this magical query I am hoping exists, get back:
> OrderNumber, BIGINT, 4
> OrderName, VARCHAR, 50
> or something in that order. Anyone have any ideas?|||David Samson (CaptainSlock@.nospam.nospam) writes:
> I'm sure there is a way using the system tables or maybe on the stored
> procedures of the master db to pull the arguments for a given stored
> procedure, and their datatypes.
> So, for a stored proc like this:
> CREATE StoredProc1
> OrderNumber AS BIGINT,
> OrderName AS VARCHAR(50)
> AS ...
> I would, using this magical query I am hoping exists, get back:
> OrderNumber, BIGINT, 4
> OrderName, VARCHAR, 50
> or something in that order. Anyone have any ideas?
SELECT c.name, t.name, c.length, c.precision, c.scale
FROM sysobjects o
JOIN syscolumns c ON o.id = c.id
JOIN systypes t ON c.xtype = t.xtype
WHERE o.name = @.name
ORDER BY c.colid
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Hi There,
Why not simply try
sp_help 'procedure name'
OR
Select the name of Procedure and press (Alt+F1) If your QA is
customized for that
With warm regards
Jatinder Singh