Transact SQL :: How To Encrypt Procedure In The View Itself Using RSA

Oct 27, 2015

How to encrypt the procedure in the view itself using RSA?

View 10 Replies


ADVERTISEMENT

Transact SQL :: Encrypt Java Code Using With Encryption Clause From Server Stored Procedure Or Function

Nov 3, 2015

How to encrypt the java application code using the 'with encryption' clause from sql server stored procedure or function.

View 3 Replies View Related

Transact SQL :: Encrypt Server Column Using Trigger

Oct 20, 2015

We have to encrypt one of the SQL Server column data at the database level, we don’t want to modify the application programs, because we use ERP application and we don’t want to reapply our customization very few months, so we are looking for an option  to encrypt and decrypt the column level data value using trigger/procedure/function,  I used  instead of trigger on that table to encrypt and decrypt the values but it is not working, we want only one user(Application Service account) have an ability to encrypt and decrypt the data, not for any other id.. I read somewhere we cannot automatically encrypt/decrypt the column value using trigger, is that true...

View 5 Replies View Related

Transact SQL :: Any Way To Encrypt Varbinary Column Data?

Nov 4, 2015

Is there a way to encrypt 'varbinary' column data?

View 9 Replies View Related

How To Encrypt The Stored Procedure ?

Feb 17, 2008

Hi,
I would like to know that how can I encrypt a stored procedure, so that it can not be decrypt by me or not by any one ? I have tried using the SQL encrypt,but there is a decrypt command which decrypt the same. I do not want any one to decrypt without a password or encrypt the stored procedure so that it can never be decrypted.
Thanking you,
 Regards..Jay

View 2 Replies View Related

Encrypt A Stored Procedure.

Feb 2, 2004

How might I encrypt a stored procedure in SQL server.

In sybase this is done with SP_HIDETEXT but SQL server doesn't appear to have this.

Is there another way?

Thanks,

View 5 Replies View Related

Encrypt Stored Procedure

May 28, 2008

How to encrypt the Text Of Stored Procedure
Pls Sir Give me Small Example


Yaman

View 1 Replies View Related

Encrypt Data In A Stored Procedure

Jul 11, 2006

I am trying to insert data in a table using a stored procedure, but somehow I cannot store the values passed by the stored procedure in the table.

Table has two fields FIRST_NAME, LAST_NAME with varbinary data type(I need to encrypt the data)

My stored procedure is as follows. Please let me know what i am doing wrong!

***************************************************************

ALTER PROCEDURE [dbo].[SP_InsertInfo]
-- Add the parameters for the stored procedure here

@FIRST_NAME varBINARY(100)
,@LAST_NAME varBINARY(100)

AS
OPEN SYMMETRIC KEY key DECRYPTION BY CERTIFICATE cert

BEGIN

SET NOCOUNT ON;

-- Insert statements for procedure here


Insert into [dbo].[INFO] (FIRST_NAME, LAST_NAME)
Values ( encryptbykey( key_guid('key'),'@FIRST_NAME'),
encryptbykey( key_guid('key'),'@LAST_NAME')
)
close SYMMETRIC KEY key

END

**********************************************
EXEC sp_InsertInfo 'larry', 'Smith'

when I run the SP, the data stored in the first_name, last_name fields are @FIRST_NAME', @LAST_NAME' instead of larry, smith respectively.

Thanks

View 4 Replies View Related

How To Encrypt All Existing Stored Procedure?

Aug 18, 2006

I know that we can CREATE PROCEDURE procedure_name WITH ENCRYPTION.

But how about if I want encrypt existing stored procedures?

Which command should I use ?

View 4 Replies View Related

Encrypt Dara Throw A Stored Procedure

Apr 4, 2007

Hi,



I'm trying to use a stored procedure to encrypt data but it dosent work fine, this is how I proceeded and that worked well




Code Snippet

CREATE PROCEDURE [dbo].[UpdateUser]



@CardNumber nvarchar(max),

@UserID int

AS

BEGIN



SET NOCOUNT ON;





DECLARE @SecretData varbinary(max)



OPEN SYMMETRIC KEY MY_SYMMETRIC_KEY

DECRYPTION BY CERTIFICATE [MY_CERTIFICATE]



DECLARE @KeyGuid AS UNIQUEIDENTIFIER

SET @KeyGuid = key_guid( 'MY_SYMMETRIC_KEY')

SET @SecretData = encryptbykey( @KeyGuid, @CardNumber)



UPDATE User

SET

CardNumber=@SecretData

Where UserID=@UserID





CLOSE SYMMETRIC KEY MY_SYMMETRIC_KEY



END





but Now I use my Stored Procedure to encrypt the data, but I'm getting bad data when I decrypt.






Code Snippet

CREATE PROCEDURE [dbo].[UpdateUser]



@CardNumber nvarchar(max),

@UserID int

AS

BEGIN





SET NOCOUNT ON;



DECLARE @SecretData varbinary(max)



exec EncryptData @CardNumber, @SecretData output



UPDATE User

SET

CardNumber=@SecretData



Where UserID=@UserID



END






Code Snippet

CREATE PROCEDURE [EncryptData]

@ClearData varchar(max),

@SecretData varbinary(max) output

WITH EXECUTE AS 'DBO'

AS

BEGIN



OPEN SYMMETRIC KEY MY_SYMMETRIC_KEY

DECRYPTION BY CERTIFICATE [MY_CERTIFICATE]



DECLARE @KeyGuid AS UNIQUEIDENTIFIER

SET @KeyGuid = key_guid( 'MY_SYMMETRIC_KEY')

SET @SecretData = encryptbykey( @KeyGuid, @ClearData)



CLOSE SYMMETRIC KEY My_SYMMETRIC_KEY

END



Any Idea how to fix this issue



Thanks in advance.

View 3 Replies View Related

Calling A Stored Procedure From A View OR Creating A #tempTable In A View

Aug 24, 2007

Hi guys 'n gals,

I created a query, which makes use of a temp table, and I need the results to be displayed in a View. Unfortunately, Views do not support temp tables, as far as I know, so I put my code in a stored procedure, with the hope I could call it from a View....

I tried:

CREATE VIEW [qryMyView]
AS
EXEC pr_MyProc


and unfortunately, it does not let this run.

Anybody able to help me out please?

Cheers!

View 3 Replies View Related

Transact SQL :: Primary Key For A View?

Apr 30, 2015

I am wondering if there is such thing as primary key for a view? Assuming, that my view is based on a single table and it's just a subset of columns (some of them renamed) including PK of the table it's based on.

The reason for my question is this - we're using Reverse POCO generator which automatically generates C# Model class and Configuration file for our tables and views. For the view is lists all the columns as a key and therefore I obviously can not use normal way of updating that view. I posted that as an issue here [URL] .... but I am thinking there is no such thing as the "primary key" for a view.

The query used to generate the classes is extremely complex already but may be it can be modified to get the PK ?

SELECT [Extent1].[SchemaName],
[Extent1].[Name] AS TableName,
[Extent1].[TABLE_TYPE] AS TableType,
[UnionAll1].[Ordinal],
[UnionAll1].[Name] AS ColumnName,
[UnionAll1].[IsNullable],
[UnionAll1].[TypeName],
ISNULL([UnionAll1].[MaxLength],0) AS MaxLength,

[Code] ......

I made a quick Google search and found this [URL] ....

It sounds as an interesting idea to try although I am not sure it will work with POCO Generator. But I'm going to try it now anyway.

View 7 Replies View Related

View Stored Procedures With Transact SQL

May 25, 1999

Hi

Does anyone the syntax or command to View Stored Procedures with Transact SQL statement. I have tried using the syscomments but there is some information missing.

I would appreciate any reply !

Thanks you for your time.

View 1 Replies View Related

Transact SQL :: How To Optimize A View That Can't Be Indexed

Jul 17, 2015

I have a view that takes 14 mins to return 3000 rows.  At some points in the execution plan, there are over 4 million rows.  The query for the View has a Distinct, a couple columns are populated by functions, there is a cross join, there is a join on a table with no keys or index, joins on non-key varchar columns and so forth.  It looks a bit like this:

SELECT DISTINCT
TOP 100 PERCENT N.Id, N.CountyName, N.OperationName,
N_LO.Name, N_LO.LastName,N_LO.Address1,
N_TO.Name, N_TO.LastName,
dbo.fn_Sections(N.Shape, s.Points) AS Sections

[code]...

The DISTINCT is in there to get rid of the extra Bob rows, because Bob has many sizes.  I need to keep the multiple sizes on Bob's row, but I don't want multiple rows of Bob.  Is there a more efficient way to do that other than using DISTINCT?

Second, word on the street is that functions are best avoided if you are going for speed.  I don't see a way to use "FOR XML PATH" outside the function, because I can't get it to work on just one column.  So I am looking for a way to produce the multiple values on one row.

View 12 Replies View Related

Transact SQL :: Size On Disk For A View?

Sep 17, 2015

I'm using MS SQL Server 2008R2, Is there a way to find size on disk for view(s) ?

View 3 Replies View Related

Transact SQL :: How To Retrieve All Tables From A View

Jun 29, 2015

We are accessing a database through Linked Servers. That database has a bunch of views.We are able to get a list of columns for our views by querying [syscolumns]. However, how do we find out which of those columns have primary keys?

View 6 Replies View Related

Transact SQL :: Running Totals In A View

Sep 14, 2015

I have a ms-sql 2012 for this task. I read that running totals / sum over is better supported here.

There are customers with an account where they can insert or withdraw cash. When they insert, the inserted amount will get a date where it expires. After the expiration date, the cash becomes unavailable.

I'm not sure how to design the tables to meet my needs, but this is my best guess. And below is the first problem in the view.

Table: deposit    This table will hold all deposits

customerId    (string)
balanceType    (string)
transactionDate   (date) 
expiresOnDate   (date)
amount     (decimal)
transactionText   (string)

The data set for the deposit table could be:

1 Bonus 01-05-2015 30-04-2016  500  Inserted 500
2 Bonus 01-05-2015 30-04-2016  500  Inserted 500
3 Bonus2 01-01-2015 31-12-2015  100  Inserted 100
2 Bonus2 01-01-2015 31-12-2015  100  Inserted 100

Table: withdrawal  This table will hold all withdrawals

customerId    (string)
balanceType    (string)
transactionDate   (date) 
amount     (decimal)
transactionText   (string)

The data set for the withdrawal table could be:

2 Bonus2 01-04-2015 -100  Needed 100
2 Bonus2 02-04-2015 -100  Needed 100
2 Bonus2 03-01-2015 -100  Needed 100
3 Bonus2 10-04-2015 -50   Took out 50
3 Bonus2 11-04-2015 -100  Took out 100

[Code] .....

Now I need to combine the two tables in a view with a running total, ordered by customerId, balanceType and transactionDate

customerId
balanceType
transactionDate
amount
Total
Text

The view must show this, when selecting all records:

1 Bonus 01-05-2015 500  500  Inserted 500
1 Bonus 01-07-2015 -100 400  Yes, got 100
1 Bonus 02-07-2015 -100 300  Yes, got 100
1 Bonus 03-07-2015 -100 200  Yes, got 100

[Code] ....

And a last view that will show distinct totals for each customer and balanceType

customerId
balanceType
totalBalance

1 Bonus -200
2 Bonus -300
2 Bonus2 -200
3 Bonus2 -550

View 8 Replies View Related

Transact SQL :: Dynamically Create A View

Jun 25, 2015

Log-in access web-page.When page is accessed a stored procedure is executed that will provide a list of all the RA(Problem -- when a new building is added, this stored procedure must be opened and syntax added)..Results are stored in a drop down list on the page.Create a table holds list of all buildings something similar to this

Create Table [dbo].[BuildingInformation] (
ID INT IDENTITY NOT NULL,
BuildingName varchar(50),
RAName varchar(50)

[code]....

Alter the stored procedure so that when it is run it will iterate the BuildingInformation table and compare the value in BuildingInformation to the values in the field BuildingName of the view [RA].  If all buildings contained in the table [BuildingInformation] exist in the view [RA] nothing will change.  If their are values in the [BuildingInformation] table that do not exist in the view [RA], the view [RA] will be dropped and recreate with a 'Union ALL' for each building in the table

View 6 Replies View Related

Transact SQL :: Combine Two Views Into One Statement Or One View

Oct 15, 2015

I'd like to get results from ZTest_Contract being my result set, and would like to combine the subquery (which gets the Max) into the primary view ZTest_Contract.

CREATE VIEW [dbo].[ZTest_Contract] AS
Select
M.CUSTNMBR,
M.ADRSCode,
M.Contract_number,
M.MaxWSCONTSQ,
M.Equipment_id,

[Code] ......

View 3 Replies View Related

Transact SQL :: Create A View For Getting Rate What Was There On Said Period

May 13, 2015

table1

id Rate start end
A01 2 01/04/2015 31/03/2016
A02 2 01/04/2015 31/03/2016
A03 2 01/04/2015 31/03/2016
A01 2.5 01/04/2016 31/03/2017
A02 2.5 01/04/2016 31/03/2017
A03 2.5 01/04/2016 31/03/2017

table2

id Received
A02 13/05/2015
A02 23/07/2016

View 3 Replies View Related

Transact SQL :: Script For Checking CPU / Memory And I/O For View

Aug 27, 2015

I am looking for script that can get details about CPU, Memory and I/O for view i.e how much memory, cpu and I/O have been used by particular view.

View 3 Replies View Related

Transact SQL :: View All Data That Have Date Of Today

Jul 31, 2015

I want to view all data that have a date of today, but my query is not returning them?  Is it due to the actual data being a datetime and I am not accounting for time?  How should I set this query up so that the data returns?

Create Table DateTest(ID int,Date1 datetime)
Insert Into DateTest Values(1, GetDate()), (2, GetDate()), (3, GetDate()), (4, GetDate())

Select * from DateTest
ORDER BY Date1 DESC

Select * from DateTest
where Date1 = convert(varchar(10), GetDate(), 126)

View 9 Replies View Related

Transact SQL :: Can Define A View To Oracle Data

Apr 30, 2015

I have a subset of data whose source of truth resides in an Oracle Database. Can I define a SQL Server View to retrieve this subset of data and make it available on the SQL Server side for say Reporting and SSRS? I think I also saw where they tend to prefer User-Defined functions as opposed to a view.

If I define the view using an OPENQUERY, I have noticed that it seems to run FOREVER on the SQL Server side and returns pretty timely if I run in Oracle SQL Developer. Is there anything I can tweak to improve this timeliness or lack there of? And the whole reason I'd like to create a view of this Oracle data is so that I can take advantage of all the views and functions that we have created on the SQL Server side.

View 4 Replies View Related

Transact SQL :: Creating A View Using DISTINCT And Not Getting Unique Results?

Sep 21, 2015

I am building a view to be used to drill down into a Lightswitch app I'm building and to drive this I want to use a view based off the selection of that value several other values will be given to the user to choose from related to the first selection. I've created a view using the following statement:

SELECT DISTINCT TOP (100) PERCENT ARSFamily, ARS_Index
FROM dbo.csr_standards_cmsars
ORDER BY ARSFamily

 but the results come back with ALL the records of the source table (509 rows) when there should have only been 29 rows returned (the appropriate number of families or unique groups).  The index is necessary to have Lightswitch use the view as a data source.what I'm doing wrong here?

View 2 Replies View Related

Transact SQL :: Way To Have Trigger Fire When Something Happens To Tables That View Is Based On?

Nov 4, 2015

I created a view that joins several tables together and added an instead of insert, update trigger on it. My trigger works when I do an update/insert directly to the view, but it doesn't seem to fire when an insert/update is made to any of the tables that the view is based on. Is there a way to have the trigger fire when something happens to the tables that the view is based on? The application that the database is using is something that was purchased so I don't have access to the code to change any SQL statements that it's using.

View 5 Replies View Related

Transact SQL :: Select From View In SSMS Doesn't Return All Rows

Jun 8, 2015

I am using SQL 2014 RTM (may be it's time to upgrade).

I have the following view:

create view [dbo].[SiriusV_Max4SaleList]
as
select m.id as Max4SaleId,
mt.[Description] as [TypeDescription],
CAST(m.[type] as tinyint) as [Type],
m.start_time as [StartTime],
m.end_time as [EndTime],

[Code] ....

I am thinking I may want to remove CAST for department, category, item later on as I don't really care if these columns would be defined as key for my EF model, but I do want to search by these columns. Anyway, this is my current view.

I executed the following select statement once

select * FROM dbo.siriusv_max4saleList where department like 's%' or category like 's%' or item like 's%'

And I believe I got 29 rows initially. However, when I execute this statement now I'm getting just 13 rows. If I execute just the department like 's%' I am getting 0 rows although I can see in the first result a row where department has s in in.

I guess I keep it here since I've created the message already but now I figured out why I am not getting the expected result. I used the condition like 's%' and not like '%s%' which application is doing.

View 4 Replies View Related

Transact SQL :: Allow A User To Select Data Through A View But Not Restrict Access To Underlying Table

Sep 29, 2015

I have two databases DB1 and DB2 DB1 has a source table named 'Source' I have created a login 'Test_user' in DB2 with Public access. I have also created a view named 'Test_view' in DB2 which references data from DB1.dbo.Source

How can I do the following: AS A Test_user

SELECT * FROM DB2.dbo.Test_view --Should work

SELECT * FROM DB1.dbo.Source --Should Not work

View 3 Replies View Related

Should I Use A View Or A Stored Procedure

Apr 5, 2007

I'm modifying a pretty big web application and the programmer who built it used all stored procedures and no views.  Does anyone know why someone would do this?  I realize that you can't pass parameters with views and insert/update/delete records with views, but he even used stored procedures for queries like: SELECT * FROM myTable WHERE myVal > 0 ORDER BY myVal Is it more efficient to put this in a stored procedure compared a view?  

View 1 Replies View Related

View Vs. Stored Procedure

Jul 7, 2000

Are there performace benefits to using a select from a View instead of a stored procedure that returns the same dataset? I am concerned about when we ramp up to 100's of users.

View 3 Replies View Related

Stored Procedure Vs View

Jan 23, 2006

I like the security of using stored procedures. It seems I am able to do anything with it that I can with a view. Why would I choose a view over a sproc?

View 3 Replies View Related

Having Store Procedure Instead Of View

Mar 17, 2015

I have the following code to create a view. Instead of having a view I would like to convert this code to create a store procedure so the data is saved in a table.

SELECT TOP (100) PERCENT StockCode, MAX(categ) AS categ, MAX(flavor) AS flavor, MAX(Type) AS Type
FROM (SELECT RTRIM(KeyField) AS StockCode, RTRIM(AlphaValue) AS categ, RTRIM(AlphaValue) AS flavor, RTRIM(AlphaValue) AS Type, DateValue
FROM companyB.dbo.AdmFormData WHERE (0 = 1)
UNION ALL

[Code] ....

View 2 Replies View Related

Stored Procedure Or View?

Mar 14, 2007

Hello all I am not quite a beginner but not an expert at SQL. I'm kind of in a bind and need some help. I have a table that shows me statuses of tickets (open, pending, closed), some tickets could have as much as 25 rows/ticket. I want to try to avoid that but at the same time keep track of the time. Here's what I need to happen...

with the data example below I need to take the ((closed date - first open date) - total of Waiting time). This will give me total time duration of the ticket. I'd like to either write a stored procedure or create a view that would do this for me. Any one have ideas?

CallID DateStopTimeStopCallStatus
002161772006-01-2005:39:24Open
002161772006-01-2005:39:27Open
002161772006-01-2005:40:13Open
002161772006-01-2005:40:24Pending
002161772006-02-0716:05:47Pending
002161772006-02-2117:26:22Pending
002161772006-02-2117:29:06Pending
002161772006-02-2117:29:08Open
002161772006-03-0316:35:10Open
002161772006-04-0515:12:26Open
002161772006-04-0515:17:09Open
002161772006-04-1414:37:49Open
002161772006-04-1414:37:54Awaiting
002161772006-04-1911:20:30Awaiting
002161772006-04-1912:12:34Awaiting
002161772006-04-1912:12:37Awaiting
002161772006-04-1912:12:58Awaiting
002161772006-04-1912:13:00Closed[/b]

View 20 Replies View Related

Stored Procedure From The View

Sep 25, 2007

How can I create a stored procedure that combines the results from three views, and puts them in a temp table?

View 20 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved