Malicious attack or SQL Command?

Hi,
Yesterday here we had an incident on one of our production servers
where a few tables were explicitly dropped and recreated. I knew this
because of the object create date and the table permissions had
disappeared. This could have been a malicious attack or some process,
however my hunch is with the former.
Since the SQL Security here is going through a complete rethink (after
the horse has bolted!), my question would be other than explicitly
dropping and recreating a table is there a SQL Command/Process that
does this? Also for future reference (in case this sort of thing
happens again) I would like to setup server-side tracing, however I
have noticed that the trace doesn't pick up a users/machines IP
Address. I can see that in the SQL Error Logs use Network Address but
can a trace explicitly pick up an IP? I know SQL Server can block
certain IPs but can it log them?
Any other ideas for preventing this sort of thing would be most
welcome. Funny, you never think about security until you *REALLY* have
to. A lesson to be learned here.
Rgds,
qhMost likely this was a person with legitimate access to the SQL Server using
Enterprise Manager (AKA Enterprise Mangler) to change a table. Sometimes EM
drops and recreates tables "under the covers" to accomplish a task that does
not have a corresponding direct T-SQL command. This can expand to multiple
tables when Referential Integrity constraints are involved. Most of the
time this doesn't cause a problem, but if the system is high volume or the
tables are large, it gac get very ugly, very quickly. Personally, I avoid
EM to do production table changes. I prefer to script everything and deploy
to a test/QA system first.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
<quackhandle1975@.yahoo.co.uk> wrote in message
news:1123148974.573160.310190@.g47g2000cwa.googlegroups.com...
> Hi,
> Yesterday here we had an incident on one of our production servers
> where a few tables were explicitly dropped and recreated. I knew this
> because of the object create date and the table permissions had
> disappeared. This could have been a malicious attack or some process,
> however my hunch is with the former.
> Since the SQL Security here is going through a complete rethink (after
> the horse has bolted!), my question would be other than explicitly
> dropping and recreating a table is there a SQL Command/Process that
> does this? Also for future reference (in case this sort of thing
> happens again) I would like to setup server-side tracing, however I
> have noticed that the trace doesn't pick up a users/machines IP
> Address. I can see that in the SQL Error Logs use Network Address but
> can a trace explicitly pick up an IP? I know SQL Server can block
> certain IPs but can it log them?
> Any other ideas for preventing this sort of thing would be most
> welcome. Funny, you never think about security until you *REALLY* have
> to. A lesson to be learned here.
>
> Rgds,
> qh
>

making JOINS

Hi alll
I have these tables below with 3 fields each. I want to get the record
in table 1 whose field number value is same in table 2 but field number
2 on both tables are different. I mean i want the record
1500 800 2. Insight: Table 1 is modules ordered and table 2 is modules
delivered. I want to get 1500 800 2.beacuse module 800 was ordered but
in table 2 module 503 was delivered. can some one help me with a join

nice weekeend

Table 1

10 5012 10
1600502
100 502 3
1500800 2

Table 2

1500503 1
14004000
100502 10
100600
100502 3MORALBAROMETER wrote:

Quote:

Originally Posted by

Hi alll
I have these tables below with 3 fields each. I want to get the record
in table 1 whose field number value is same in table 2 but field number
2 on both tables are different. I mean i want the record
1500 800 2. Insight: Table 1 is modules ordered and table 2 is modules
delivered. I want to get 1500 800 2.beacuse module 800 was ordered but
in table 2 module 503 was delivered. can some one help me with a join
>
nice weekeend
>
Table 1
>
10 5012 10
1600502
100 502 3
1500800 2
>
>
>
>
Table 2
>
1500503 1
14004000
100502 10
100600
100502 3
>

Write a statement where:
field1 = field1
and
field2 <field2
--
Daniel A. Morgan
University of Washington
damorgan@.x.washington.edu
(replace x with u to respond)|||Hi Daniel,
thanks alot for the prompt reply. this is what i had:

100 502
100 502
1500 800

as seen in the table 100 502was ordered and shipped but more articles
were shipped not ordered by customerID(100). I do not need this
information. I need a list where a customer ordered and received
shippment where no product ordered was shipped. in this case
customer(1500) should be the right answer. He ordered and recived
shippement but within the list no article oredered was shiped. but
customer (100) received the ordered product plus some extra
Hope to read from u and any one else

DA Morgan wrote:

Quote:

Originally Posted by

MORALBAROMETER wrote:

Quote:

Originally Posted by

Hi alll
I have these tables below with 3 fields each. I want to get the record
in table 1 whose field number value is same in table 2 but field number
2 on both tables are different. I mean i want the record
1500 800 2. Insight: Table 1 is modules ordered and table 2 is modules
delivered. I want to get 1500 800 2.beacuse module 800 was ordered but
in table 2 module 503 was delivered. can some one help me with a join

nice weekeend

Table 1

10 5012 10
1600502
100 502 3
1500800 2

Table 2

1500503 1
14004000
100502 10
100600
100502 3

>
Write a statement where:
field1 = field1
and
field2 <field2
--
Daniel A. Morgan
University of Washington
damorgan@.x.washington.edu
(replace x with u to respond)

|||On 23 Oct 2006 00:52:26 -0700, MORALBAROMETER wrote:

Quote:

Originally Posted by

>Hi Daniel,
>thanks alot for the prompt reply. this is what i had:
>
>100 502
>100 502
>1500 800
>
>as seen in the table 100 502was ordered and shipped but more articles
>were shipped not ordered by customerID(100). I do not need this
>information. I need a list where a customer ordered and received
>shippment where no product ordered was shipped. in this case
>customer(1500) should be the right answer. He ordered and recived
>shippement but within the list no article oredered was shiped. but
>customer (100) received the ordered product plus some extra
>Hope to read from u and any one else

Hi MORALBAROMETER,

Maybe something like this?

SELECT o.CustomerID, o.ArticleID
FROM Orders AS o
LEFT JOIN Shipments AS s
ON s.CustomerID = o.CustomerID
AND s.ArticleID = o.ArticleID
WHERE s.CustomerID IS NULL

or the following (logically equivalent, but easier to understand for
beginning SQL coders):

SELECT o.CustomerID, o.ArticleID
FROM Orders AS o
WHERE NOT EXISTS
(SELECT *
FROM Shipments AS s
WHERE s.CustomerID = o.CustomerID
AND s.ArticleID = o.ArticleID)

If these are not what you're after, then I recommend that you post your
table structure (as CREATE TABLE statements, including constraints,
properties and indexes), some rows of sample data (as INSERT statements)
and expected results. See www.aspfaq.com/5006 for more info.

--
Hugo Kornelis, SQL Server MVP

Making Identity to be Not for replication

Hello there
I have many tables on my database (Approx 500)
Many of them have identity column and none of them are set for Not for
replication
Is there a way to change it by code automaticly?
try this
http://groups.google.com/group/micro...5?dmode=source
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Roy Goldhammer" <roy@.hotmail.com> wrote in message
news:e5gD6%23vAGHA.516@.TK2MSFTNGP15.phx.gbl...
> Hello there
> I have many tables on my database (Approx 500)
> Many of them have identity column and none of them are set for Not for
> replication
> Is there a way to change it by code automaticly?
>

Making changes to the reportserver database

Is it ok to make changes directly to the ReportServer database - adding
tables and stored procedures? Will admendments I make affect upgrades in the
future?My feeling is that you do this at your own risk. Going directly at the
reportserver database is not supported. You should use the web services. If
you want some of you own tables and stored procedures I would suggest
creating another database, even if you are going against reportserver
database.
I would guess that you could do it as long as your naming convention is such
that MS wouldn't be likely to name something similar. But if you put them in
your own database then your are guaranteed to be safe.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Si Downes" <SiDownes@.discussions.microsoft.com> wrote in message
news:093F8E5E-948B-40BE-A4BE-14AB46A080EF@.microsoft.com...
> Is it ok to make changes directly to the ReportServer database - adding
> tables and stored procedures? Will admendments I make affect upgrades in
the
> future?

Making a Select between two diferent SQL Servers...Urgent please Help

Hi there friend...I'm in a midle of a program(vb.net). and i've got this problem, I've got do compare two tables from two diferent sql servers. Is this possible? How can I do this?

Please, this is really urgent.

Thank you for your attentionLook into Linked Servers in Books Online. Then you can reference another server once it's linked to your SQL Server. HTH|||Tnks, i will do that.

Making "incremental" updates to a data warerhouse

We have a legacy database whose data needs to be included in our yet-to-be-built sql 2005 data warehouse. Some of the tables don’t have any natural candidates for a primary key. (Of course, we also need to add other data to the mix.)

Suppose we load the empty warehouse initially. In following loads we don’t want to include those records that haven’t changed from the first load (“duplicates”) but we also don’t want to delete the contents of the entire warehouse because of the load time.Any ideas/best practices how to handle “incremental updates” to a warehouse would be appreciated.

TIA,

Bill

<<tables don’t have any natural candidates for a primary key>>

The above situation is likely a fundamental design flaw for which there is no easy fix. What is the usefullness and business meaning of an entity for which there is no natural key? It is likely nonsense.

However, from your definition of the problem, it sounds more like the key would be the combination of all columns in the row. How else would you define what is a "new" row vs. a "changed" row? Isn't that what you are describing? If so, there is your key.

Ken

|||

You can use the Slowly Changing Dimension Wizard, an advanced transformation component, to create this type of process quite easily.

|||

Can you explain how "slowly changing dimensions" can be used to create this type of process? (I'm new to this stuff and I can't make the connection on my own.)

TIA,

Bill

|||

Here's a few references:

http://blogs.conchango.com/jamiethomson/archive/2005/06/06/1543.aspx (includes a demo)

http://sqljunkies.com/WebLog/tpagel/archive/2005/07/24/16195.aspx

Probably the best available reference is BOL.

-Jamie

Making "incremental" updates to a data warerhouse

We have a legacy database whose data needs to be included in our yet-to-be-built sql 2005 data warehouse. Some of the tables don’t have any natural candidates for a primary key. (Of course, we also need to add other data to the mix.)

Suppose we load the empty warehouse initially. In following loads we don’t want to include those records that haven’t changed from the first load (“duplicates”) but we also don’t want to delete the contents of the entire warehouse because of the load time.Any ideas/best practices how to handle “incremental updates” to a warehouse would be appreciated.

TIA,

Bill

<<tables don’t have any natural candidates for a primary key>>

The above situation is likely a fundamental design flaw for which there is no easy fix. What is the usefullness and business meaning of an entity for which there is no natural key? It is likely nonsense.

However, from your definition of the problem, it sounds more like the key would be the combination of all columns in the row. How else would you define what is a "new" row vs. a "changed" row? Isn't that what you are describing? If so, there is your key.

Ken

|||

You can use the Slowly Changing Dimension Wizard, an advanced transformation component, to create this type of process quite easily.

|||

Can you explain how "slowly changing dimensions" can be used to create this type of process? (I'm new to this stuff and I can't make the connection on my own.)

TIA,

Bill

|||

Here's a few references:

http://blogs.conchango.com/jamiethomson/archive/2005/06/06/1543.aspx (includes a demo)

http://sqljunkies.com/WebLog/tpagel/archive/2005/07/24/16195.aspx

Probably the best available reference is BOL.

-Jamie

sql

Make a Extra COUNT Coulmn on result

Hi Champs!
This is probably simple for you champs, but!
I hava tricky SQL query I cannot get to work.
I have a Query from two tables A and B:
I Make a SELECT
A.time , B.name , B.number , B.ammount , A.date , A.transactionNumber
This “TransactionNumber” is in table A and B
So a readout looks like this:
A.time , B.name , B.number , B.ammount , A.transactionNumber
14:00 -- toy1 -- 23423 -- 1 -- 15889
14:00 -- toy2 -- 23488 -- 2 -- 15889
14:04 -- toy8 -- 11423 -- 5 -- 15890
14:10 -- toy2 -- 23488 -- 10 -- 15891
14:10 -- toy3 -- 23473 -- 1 -- 15891
14:10 -- toy6 -- 11342 -- 17 -- 15891
14:10 -- toy9 -- 23563 -- 2 -- 15891
14:12 -- toy0 -- 23423 -- 1 -- 15892
14:12 -- toy4 -- 23423 -- 3 -- 15892
So I need to make a one New Extra column after A.transactionNumber, that
counts the that similar numbers; like this:
A.time , B.name , B.number , B.ammount , A.transactionNumber , Extra New
column
14:00 -- toy1 -- 23423 -- 1 -- 15889
-- 1 --
14:00 -- toy2 -- 23488 -- 2 -- 15889
-- 2 --
14:04 -- toy8 -- 11423 -- 5 -- 15890
-- 1 --
14:10 -- toy2 -- 23488 -- 10 -- 15891
-- 1 --
14:10 -- toy3 -- 23473 -- 1 -- 15891
-- 2 --14:10 -- toy6 -- 11342 -- 17
-- 15891 -- 3 --
14:10 -- toy9 -- 23563 -- 2 -- 15891
-- 4 --
14:12 -- toy0 -- 23423 -- 1 -- 15892
-- 1 --
14:12 -- toy4 -- 23423 -- 3 -- 15892
-- 2 --
I’ve tried with the a Count (*) , but cannot seem to get it to work.
Any help would be much appreciated.
/Many Thanks
Kurlan"Kutlan" <Kutlan@.discussions.microsoft.com> wrote in message
news:B7791534-DEBC-48CD-AD63-F6F167897B60@.microsoft.com...
> Hi Champs!
> This is probably simple for you champs, but!
> I hava tricky SQL query I cannot get to work.
> I have a Query from two tables A and B:
> I Make a SELECT
> A.time , B.name , B.number , B.ammount , A.date , A.transactionNumber
> This "TransactionNumber" is in table A and B
> So a readout looks like this:
> A.time , B.name , B.number , B.ammount , A.transactionNumber
> 14:00 -- toy1 -- 23423 -- 1 -- 15889
> 14:00 -- toy2 -- 23488 -- 2 -- 15889
> 14:04 -- toy8 -- 11423 -- 5 -- 15890
> 14:10 -- toy2 -- 23488 -- 10 -- 15891
> 14:10 -- toy3 -- 23473 -- 1 -- 15891
> 14:10 -- toy6 -- 11342 -- 17 -- 15891
> 14:10 -- toy9 -- 23563 -- 2 -- 15891
> 14:12 -- toy0 -- 23423 -- 1 -- 15892
> 14:12 -- toy4 -- 23423 -- 3 -- 15892
>
> So I need to make a one New Extra column after A.transactionNumber, that
> counts the that similar numbers; like this:
> A.time , B.name , B.number , B.ammount , A.transactionNumber , Extra New
> column
> 14:00 -- toy1 -- 23423 -- 1 -- 15889
> -- 1 --
> 14:00 -- toy2 -- 23488 -- 2 -- 15889
> -- 2 --
> 14:04 -- toy8 -- 11423 -- 5 -- 15890
> -- 1 --
> 14:10 -- toy2 -- 23488 -- 10 -- 15891
> -- 1 --
> 14:10 -- toy3 -- 23473 -- 1 -- 15891
> -- 2 --14:10 -- toy6 -- 11342 -- 17
> -- 15891 -- 3 --
> 14:10 -- toy9 -- 23563 -- 2 -- 15891
> -- 4 --
> 14:12 -- toy0 -- 23423 -- 1 -- 15892
> -- 1 --
> 14:12 -- toy4 -- 23423 -- 3 -- 15892
> -- 2 --
>
> I've tried with the a Count (*) , but cannot seem to get it to work.
> Any help would be much appreciated.
>
> /Many Thanks
> Kurlan
>
Would a GROUP BY WITH ROLLUP work for you? Check it out in the BOL.
Your query would then look something like: (note untested).
SELECT A.time ,
B.name ,
B.number ,
B.ammount ,
A.date ,
A.transactionNumber
COUNT(A.transactionNumber)
FROM TableA a
JOIN TableB b ON a.TransactionNumber = b.TransactionNumber
GROUP BY a.time, b.name, b.number, b.ammount, a.date, a.transactionNumber
WITH ROLLUP
Rick|||What is the primary key? Please post DDL for the table otherwise any
answers you get will just be guesswork. It's also more helpful to
include sample data as INSERT statements rather than tabular text.
Here's an example from the Pubs database that may give you a clue.
SELECT A1.au_id, A1.au_lname, A1.au_fname, A1.state,
COUNT(*) AS num
FROM Authors AS A1
JOIN Authors AS A2
ON A1.state = A2.state
AND A1.au_id >= A2.au_id
GROUP BY A1.au_id, A1.au_lname, A1.au_fname, A1.state
ORDER BY A1.state, A1.au_id ;
David Portas
SQL Server MVP
--

Make 1 query from 2 tables

Hi,
Is it possible with SQL to create 1 query from 2 tables?
Table1 and Table2 have a few corresponding columns but mostly different
ones. Now I need 2 of these columns and get them in a query. Can I get the
values from table in there together with table? The tables have the same
column.
How can this be done?
Thanks
JorisJoris De Groote,
Please, provide tabls schema, sample data and expected results.
Please provide DDL and sample data.
http://www.aspfaq.com/etiquette.asp?id=5006
AMB
"Joris De Groote" wrote:

> Hi,
> Is it possible with SQL to create 1 query from 2 tables?
> Table1 and Table2 have a few corresponding columns but mostly different
> ones. Now I need 2 of these columns and get them in a query. Can I get the
> values from table in there together with table? The tables have the same
> column.
> How can this be done?
> Thanks
> Joris
>
>|||Joris De Groote wrote:
> Hi,
> Is it possible with SQL to create 1 query from 2 tables?
> Table1 and Table2 have a few corresponding columns but mostly different
> ones. Now I need 2 of these columns and get them in a query. Can I get the
> values from table in there together with table? The tables have the same
> column.
> How can this be done?
> Thanks
> Joris
Begin by referring to Books Online, look up the SELECT statement, in
particular the JOIN clause.|||Check out these links and read up on SQL a bit...
http://www.w3schools.com/sql/sql_intro.asp
http://sqlzoo.net/
"Joris De Groote" <joris.degroote@.skynet.be> wrote in message
news:u$f9iUWiGHA.4144@.TK2MSFTNGP02.phx.gbl...
> Hi,
> Is it possible with SQL to create 1 query from 2 tables?
> Table1 and Table2 have a few corresponding columns but mostly different
> ones. Now I need 2 of these columns and get them in a query. Can I get the
> values from table in there together with table? The tables have the same
> column.
> How can this be done?
> Thanks
> Joris
>

Major Time Out Issue

I have created a database with three tables. The database has been up for a month now and contains about 20,000 records. In order to improve performance and resolve some issues I an attempting to change some of the table information. i.e. allow nulls in a few fields. When I use TSQL or the GUI to make these changes I get the following error: Timeout expired. The timeout period elapsed prior to completion of the operation or server not responding.

Source: .Net SQLClient Data Provider

I have SQL Server Express SP2 installed with .Net framework v3.0

Note: This issue appears when I attempt to delete a row from a table as well.

Any thoughts?

It is difficult to guess -there isn't enough information.

If users are in the database at the same time as you are attempting to make table wide changes, you could be experiencing 'blocking' behavior. Try making your changes when there are no users in the database. (Twenty thousand records is a very small table and most changes should happen relatively quickly.)

You might also verify the indexing.