SQL Server 2008 :: Script Out Tables With Encrypted Triggers?

May 14, 2015

I am trying to run a test migration of our application from V8 to V9. To do this I am creating an empty database by running the Tasks -> Script database

Once that is done, I restore it to our dev server and run SQLCompare against it to generate the change scripts

The problem I am facing is that there are a few tables that have encrypted triggers on them and I cannot include them in the generate scripts. I want to take the table structure, including indexes, but I am not interested in the triggers. There are only 5 tables so I could script them by hand, but this means that I cannot automate the process.

As a result of the missing tables SQLCompare will try to generate a CREATE TABLE rather than an ALTER TABLE DDL

The live migration won't be a problem because this will be a database restore and migration - my problem is the CREATE TABLE DDLs in the script.

View 6 Replies


ADVERTISEMENT

SQL Server 2008 :: Using Encrypted Value In Select-where

Mar 6, 2015

The passwords in table 'users', column 'passwordencrypted' are encrypted. Someone enters a password and I'd like to compare if it is correct. The syntax below seems ok but nothing is returned. Why not?

OPEN SYMMETRIC KEY mykey DECRYPTION BY CERTIFICATE mycert;
DECLARE @mypw varchar(300);
SET @mypw = 'test';
SELECT * FROM users WHERE passwordencrypted = EncryptByKey(Key_GUID('mykey'), @mypw);
CLOSE SYMMETRIC KEY mykey;

View 3 Replies View Related

SQL Server 2008 :: How To Remove ENCRYPTED From A FUNCTION

Aug 27, 2015

I have a Table-valued Function already defined in my DB. When I look at it's Properties, it lists Encrypted TRUE.

How can I set Encrypted to FALSE? Is there a t-sql command to ALTER the Function to set Encrypted False?

(fyi, I am syadmin and local admin on this SQL 2008 R2 box)

View 1 Replies View Related

SQL Server 2008 :: How To Check If The Database Itself Is Encrypted Or Only The Backups

Jul 9, 2015

I queried sys.databases in one of the sql server and found "is_encrypted" is "1" for four of the databases.

Does that mean that all those 4 DBs are encrypted ?

How to check if the database itself is encrypted or only the backups ?

Note :- I can see backups of key in a particular folder.

View 9 Replies View Related

SQL Server 2008 :: How To Set Up Triggers After SSIS Dump

May 11, 2015

I am would like for a Trigger to fire after an SSIS job finishes.

My understanding is that i would use a AFTER trigger.

How my UPDATE and INSERT INTO would fire and only affect the new rows.

SET ARITHABORT OFF
SET ANSI_WARNINGS OFF

UPDATE [GDev].[dbo].[tblCIDetailsTest]
SET dFRate = (dFCharge/(dSCharge+dACharge))

Also need to INSERT INTO 3 columns from a Table called tblFinanceP by looking up/Union or Join (not sure what to use) called vcTNum that is in bother the tblFinacneP and tblCIDetailsTest.

INSERT INTO [GDev].[dbo].[tblCIDetailsTest] AS Details
SELECT iPNum, iPCount, iZone
FROM [GrEDI].[dbo].[tblFinanceP] AS EDI
where EDI.vcTNum = GDev.dbo.tblCIDetailsTest.vcTN

View 1 Replies View Related

Encrypted Tables

Apr 28, 2000

Hi there,
I was wondering if anyone knew of a way to encrypt a table?
I now you can encrypt Stored Procedures, triggers and view, but
can you encrypt a table??

Let me know
Thanks
Susan

View 1 Replies View Related

Joing 2 Tables On An Encrypted Column

Mar 18, 2008

Hi all, 
I am trying to join 2 tables in my database on an encrypted column.  I am using a symmetric encryption, but because of the IV, a string encrypted at 2 different times produces 2 different encrypted strings.  Therefore, I can not merely join the 2 tables on the encrypted columns.  Has anyone any insight on techniques for solving this problem?  The most naïve way, would be to decrypt both tables, join them, and re-encrypt them again.  But, the tables are quite large, and this would be very processor (and time) intensive. 
Any help would be greatly appreciated.  Thanks in advance for any suggestions.
 

View 3 Replies View Related

SQL Server 2008 :: Alter Table Triggers Recreate Table?

May 26, 2015

which ALTER TABLE/ALTER COLUMN- Statement has a Recreate Table as result ?

View 2 Replies View Related

Problem Restoring Database With Encrypted Columns To Different Database Or Server With Encrypted Columns.

Jan 23, 2006

I need to start encrypting several fields in a database and have been doing some testing with a test database first. I've run into problems when attempting to restore the database on either the same server (but different database) or to a separate server.

First, here's how i created the symmetric key and encrypted data in the original database:

create master key
encryption by password = 'testAppleA3';

create certificate test
with subject = 'test certificate',
EXPIRY_DATE = '1/1/2010';

create symmetric key sk_Test
with algorithm = triple_des
encryption by certificate test;

open symmetric key sk_Test decryption by certificate test;

insert into employees values (101,'Jane Doe',encryptbykey(key_guid('sk_Test'),'$200000'));
insert into employees values(102,'Bob Jones',encryptbykey(key_guid('sk_Test'),'$500000'));

select * from employees
--delete from employees
select id,name,cast(decryptbykey(salary) as varchar(10)) as salary from employees

close all symmetric keys

Next I backup up this test database and restore it to a new database on a different server (same issue if restore to different database but on same server).

Then if i attempt to open the key in the new database and decrypt:

open symmetric key sk_Test decryption by certificate test;

I get the error: An error occurred during decryption.

Ok, well not unexpected, so reading the forums, i try doing the below first in the new database:

ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY

Then I try opening the key again and get the error again:

An error occurred during decryption.

So then it occurs to me, maybe i need to drop and recreate it so i do

drop symmetric key sk_test

then

create symmetric key sk_Test
with algorithm = triple_des
encryption by certificate test;

and then try to open it.

Same error!

So then i decide, let's drop everything, the master key, the certificate and then symmetric key:

drop symmetric key sk_test
drop certificate test
drop master key

Then recreate the master key:

create master key
encryption by password = 'testAppleA3';

Restore the certificate from a backup i had made to a file:

CREATE CERTIFICATE test
FROM FILE = 'c:storedcertsencryptiontestcert'

Recreate the symmetric key again:

create symmetric key sk_Test
with algorithm = triple_des
encryption by certificate test;

And now open the key only to get the error:

Cannot decrypt or encrypt using the specified certificate, either because it has no private key or because the password provided for the private key is incorrect.

So what am I doing wrong here? In this scenario I would appear to have lost all access to decrypt the data in the database despite restoring from a backup which restored the symmetric key and certificate and i obviously know the password for the master key.

I also tried running the command

ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY

again but this does not resolve the issue.



Thx.

View 6 Replies View Related

SQL Server 2008 :: Linking Two Tables To One

Feb 6, 2015

I have table1(Client widgets) and Table2(Client Toys) I want to link both Table1 and table2 up to Table3(ClientNames)

All tables have a clientid field

I want to right join them so I get all the client widgets and Client toys for each client...

View 2 Replies View Related

SQL Server 2008 :: Creating Tables From ERD

Mar 3, 2015

I need to write create table statements for the er diagram that I attached. I am new to sql and I have trouble integrating foreign keys with these bigger er diagrams.

These are the tables I need to create:
Create Table Author(...)
Create Table Writes(...)
Create Table Book(...)
Create Table Copy(...)
Create Table Loan(...)
Create Table Customer(...)

View 1 Replies View Related

SQL Server 2008 :: Join And SUM Values From 2 Tables

Jan 29, 2015

I am new in SQL and i need do a query where I need sum values from 2 tables, when i do it the Sum values are not correct. this is my query

SELECT D.Line AS Line, D.ProductionLine AS ProductionLine, D.Shift AS Shift, SUM(CAST(D.DownTime AS INT)) AS DownTime,
R.Category, SUM(Cast(R.Downtime AS INT)) AS AssignedDowntime,
CONVERT(VARCHAR(10), D.DatePacked,101) AS DatePacked
FROM Production.DownTimeReason R
left JOIN Production.DownTimeHistory D

[Code] .....

View 3 Replies View Related

SQL Server 2008 :: Iterate Through Database Tables?

Mar 18, 2015

MS SQL 2008.I want to execute a delete query on certain tables in my database to delete some rows in the tables.The tables selected has a certain name pattern (the name ends with "Temp").

So I can do this to get a list of the table names

SELECT name
FROM sys.Tables where
name like '%Temp'

Now I want to check each table to see if it has a column with the name "DateStamp" and then execute a delete query as follows:

delete form TableName where
DateStamp is < '2010-01-01'

In other words I need to iterate through the tables names, How to do this?

View 4 Replies View Related

SQL Server 2008 :: How To Update Certain Column From All Tables Within DB

Mar 19, 2015

I have a query, I am trying to update a certain column in my query you can see that is hard coded. The column that I am trying to update is "O_Test" I used a select statement trying to figure out how many records that accounts for with the entire database of all the tables. I got 643 records. So I am wondering if there is a way I can update all the columns without looking up each table and updating each one. This Update statement wont work because I am accounting for all records in the DB of all tables associated of what I hard coded

SELECT t.name AS table_name,
SCHEMA_NAME(schema_id) AS schema_name,
c.name AS column_name
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
WHERE c.name LIKE '%O_Test%'
ORDER BY schema_name, table_name;

View 5 Replies View Related

SQL Server 2008 :: Finding Column Within All Tables In DB

May 1, 2015

I am trying to find a way where I can search for a column that is associated in all tables of the database. I have created a query but is not executing correctly.

SELECT t.name AS table_name,
SCHEMA_NAME(schema_id) AS schema_name,
c.name AS column_name
FROM sys.tables AS t8
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
WHERE c.name LIKE '%Status%'
ORDER BY schema_name, table_name;

View 3 Replies View Related

SQL Server 2008 :: Finding Tables With No Createdate?

May 4, 2015

In our Production db we have all most all tables have the column created or createdate.

I need to find out all tables without the created or createdate column

SELECT t8.name AS table_name,
SCHEMA_NAME(schema_id) AS schema_name,
c.name AS column_name
FROM sys.tables AS t8
INNER JOIN sys.columns c
ON t8.OBJECT_ID = c.OBJECT_ID

[code]....

View 2 Replies View Related

SQL Server 2008 :: Large Tables In OLTP

Jul 14, 2015

How many no of records of the tables are called large tables.

We are getting more deadlocks. We are using default isolation. Read & insert statements are blocking each other and causes dead locks.

I am thinking that might be purging will reduce deadlocks.

The table has 15million records. Is this table consider as large table or not in OLTP systems?

In general how many records we need to consider as large table.

View 1 Replies View Related

SQL Server 2008 :: Comparing Tables For Like Values?

Jul 17, 2015

I have a table of raw data with supplier names, and i need to join it to our supplier database and pull the supplier numbers.

The issue is that the raw data does not match our database entries for these suppliers; sometimes there are extra periods, commas, or abbreviations (i.e. FedEx, FederalExpress, FedEx, inc.) etc. I'm trying to create a query that will search for entries that are similar.

I tried setting a variable to be equal to the raw data field, and then using a LIKE '%@Variable%' to try and return anything that would contain it, but it didnt return any rows.

View 9 Replies View Related

SQL Server 2008 :: Finding All Relations Between Tables?

Sep 1, 2015

Below I have a query which list the relations (constraints) between tables.

I want to list all the relations which are visible in the Database Diagrams.

The list is not complete. How do I get a complete list ?

--
-- Query to show the relations (constraints) between tables.
-- This does not show the complete list
--
SELECT A.constraint_name,
B.table_name AS Child,
C.table_name AS Parent,

[Code] ...

View 4 Replies View Related

SQL Server 2008 :: How To Encrypt Either Columns Or Tables

Nov 2, 2015

how to encrypt a either columns or tables in SQL server (2008 R2).

Do I need to encrypt a whole table of can I encrypt certain columns of a table ?

View 2 Replies View Related

SQL Server 2008 :: Finding Only Fields That Don't Match In Two Different Tables

Feb 12, 2015

I have two table People and Employee, both have firstname and lastname as fields

I want to display only the names that don't match on firstname and lastname

View 3 Replies View Related

SQL Server 2008 :: Service Broker And Temp Tables

Feb 12, 2015

Let's say that I have a stored proc that is assigned to a service broker queue and is constantly running while it waits for messages in said queue. When a message comes in on the queue, the stored proc creates a table variable based off of the contents of the message and performs various operations with the data. Since the stored proc is constantly running, do the contents of this table variable ever truly get emptied? Should I be deleting the contents of the table variable at the end of the operation to ensure that stale data doesn't persist?

View 5 Replies View Related

SQL Server 2008 :: Identify Columns And / Or Tables From Records

Mar 3, 2015

I'm using MS SQL Server 2008 and I'm trying to figure out if it is possible to identify what tables / columns contain specific records.

In the example below information generated for the end user, so the column headers (Customer ID, Customer, Address, Phone, Email, Account Balance, Currency) are not necessarily the field names from the relevant tables, they are simply more identifiable headers for the user.

Customer ID CustomerAddress Phone Email Account Balance Currency
js0001 John Smith123 Nowhere Street555-123-456 jsmith@nowhere.com-100 USD
jd2345 Jane Doe 61a Down the road087-963258 jdoe@downthe road.com-2108 GBP
mx9999 Mr X Whoknowsville 147-852369 mrx@whoknows.com0 EUR

In reality the column headers may be called eg (CustID, CustName, CustAdr, CustPh, CustMail, CustACBal, Currency).

As I am not the generator of this report, I would like to know whether or not it is possible to identify the field names and / or what tables they exist in, if I were to used the report info to search for it. For example, could I perhaps find out the field name and table for "jd2345" or for "mrx@whoknows.com", because the Customer ID or Email may not be what the actual fields are called.

I'm not a DB admin and I don't have rights to do a stored procedure on the server. I'm guessing what I want is not so simple to do, but is it possible to do via a query?

View 2 Replies View Related

SQL Server 2008 :: Moving Tables To New File Group

Mar 16, 2015

I'm presented with an issue where by I need to reclaim a fair bit of unused space currently sat in the primary data file for my database. I don't want to run DBCC SHRINKDATABASE as we all know this could potentially have a some serious negative effects relating to index fragmentation.

So, how do I get the free space out of the data file? - I've decided to:

1. Add new new file group
2. Add a clustered index for all tables on the new file group
3. Shrink the primary file group as much as possible (hopefully giving me the free space back)
4. Drop the newly created clustered indexes for all tables

There are no clustered indexes currently for any of the tables!, so me temporarily creating/dropping one shouldn't be an issue. Are there any other ways I can get the free space back to the OS?

View 8 Replies View Related

SQL Server 2008 :: Move Tables And SP From Master To User DB

Apr 28, 2015

I just notice that my MASTER database has some user tables and user SP ..and I am thinking to move them to 1 new user database but I am worried it will break something ..

What should I do ?

Moreover I wonder why Transaction log of MASTER can be full ( The recovery model is simple ) It should be fine , isn’t it?

View 5 Replies View Related

SQL Server 2008 :: Temp Tables Persisting In TembDB

Apr 30, 2015

I've recently started a new position and our production box. Contains a procedure that uses 30 + temp tables. I'm currently not in a position to change this as it's production and I would have to be granted a window to re-design.

However the tempDb is showing some strange activity.

If a table is created #CarrierService (CarrierServiceID,DeliveryZoneID,CollectionZoneID) for example

Once the procedure is called It will appear in the tempDB with the session info appended as expected

#CarrierService________________________________________________________2C78E45A

However once the session has ended the above table will get dropped and a new one created

#2C78E45A, I now have 7000 of these different Tables in the TempDB

When I Interrogate this using

SELECT o.name, o.create_date,o.modify_date , c.Name,C.Column_Id
FROM tempdb.sys.Objects o
Inner join tempdb.Sys.Columns c
ON o.object_id =c.Object_ID
WHERE o.type ='U'

I get the Following results

name create_date modify_date object_id name
#2C78E45A26/04/2015 18:0930/04/2015 14:55746120282CarrierServiceId
#2C78E45A26/04/2015 18:0930/04/2015 14:55746120282CollectionZoneId
#2C78E45A26/04/2015 18:0930/04/2015 14:55746120282DeliveryZoneId

Notice How It's getting Modified today.

View 9 Replies View Related

SQL Server 2008 :: Temp DB Size Values From Different Tables

May 25, 2015

For finding size values of temp database:

select * from sys.master_files -
size column value here is 1024 for .mdf,size here for .ldf is 64
select * from tempdb.sys.database_files -
size column value here is 3576 for .mdf,size here for .ldf is 224

Why is there a difference and not the same. size columns in the above 2 tables for temp db's do they represent different values ?

View 1 Replies View Related

SQL Server 2008 :: How Temp Tables Associated To Deferred Recompilation

May 25, 2015

1) "Deferred compile" recompile event occurs because of deferred name resolution. In other words, an object referred to in the statement does not exist at compile time. Later, when the object does exist, it requires a recompile of the statement so that it can create an optimal execution plan. One example of when a deferred compile will occur is if a temporary table is used in a batch and does not exist when the first statements in the batch are compiled.

View 2 Replies View Related

SQL Server 2008 :: Specific Type Of Join For 3 Tables

Jul 3, 2015

I have 3 tables as per following:

orddet
OrderProductQtyOrd
1 Item1 20
2 Item1 10
3 Item2 10
4 Item1 5
4 Item2 5

ordhead
OrderDate
110/06/2015
205/07/2015
307/06/2015
415/08/2015

product
ProductdescMinQty
Item1This is 110
Item2This is 220

I want to pull only the 1 line for minqty for an item as follows

OrderProductQtyOrddate minqty
1 Item1 20 10/06/2015 10
2 Item1 10 05/07/2015
3 Item2 10 07/06/2015 20
4 Item1 5 15/08/2015
4 Item2 5 15/08/2015

View 2 Replies View Related

SQL Server 2008 :: Using EXEC Functions And Temp Tables?

Jul 14, 2015

here's an example of what I am trying to do.

--Exec Database.Employees
--Use Database
--Go
--Create PROCEDURE AEM.TempTable
--AS
--BEGIN
--Select * into #emptemp From Database.Employees
--End
--Select * From #emptemp

Is something like this possible? I can get the EXEC to run the "Select * into #emptemp From Database.Employees" statement, but when I try to use the temp table it doesnt see it.

View 7 Replies View Related

SQL Server 2008 :: Join Two Tables With Specific Data

Jul 15, 2015

I have two queries from two different tables ex ABC and BCD. For table ABC, according to my query, I got 11 records ; for table BCD I only got 9 records.

Bottom line: I would like to see only 11 records from Table ABC including certain data from table BCD after I joined this two tables.

However, no matter what I did I always got 99 records when I joined.

View 3 Replies View Related

SQL Server 2008 :: Dynamic Dataset And Tables In SSRS

Jul 27, 2015

Need to change a Excel report to SSRS.

Excel report has around 15 tables all with different columns.

Is there a way , I can show all data in SSRS by avoiding creation of 15 datasets and 15 tables.

Note -- All 15 tables have differnt columns list.

View 3 Replies View Related

SQL Server 2008 :: Comparing A Column Between Two Tables With A Wildcard?

Aug 9, 2015

I have a table containing records of criminal convictions. There are over 1M records and the only change is additions to the table on a monthly basis. The two columns I need to deal with are convicted.NAME and convicted.DOB

I have a second table that has 2 columns. One is the name of the defendant and the other is the birth date. This would be monitor.NAME and monitor.DOB

There are no primary keys or any other way to join the tables for this search I want to do.

I would like to be able to put a name in the "monitor" table and run a query to see if there is a match in the convicted table.

The problem I am having is middle initials or names. If I want to monitor.name = 'SMITH JOHN' it will return the results fine. The problem I am having is if the conviction is in the database as 'SMITH JOHN T', or 'SMITH JOHN THOMAS'.

How can I use the monitor table with a 'LASTNAME FIRSTNAME' and return results if the convicted table has a middle initial. I tried with a JOIN:

select distinct convicted.*
from convicted
join monitor
on monitor.name like convicted.defendant
and monitor.birthdate = convicted.dob

View 5 Replies View Related







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