Database compromise - need help

Hello,
Our db has been compromised. A column in the table has been changed to
something unwarranted for. I need suggestions on how to track this or at
least give me some clues on how these bastard made into our database.
Besides looking at the event logs on windows (security & system) and sql
logs in SQL Enterprise Manager, what/where else can I look at? At this point
what can i do to prevent this from happening again?
We are on sql 2000 sp4.
Also, one of our collegues went ahead and remove that column so it won't
display it in the website.
Besides restoring the whole database, can I restore the database from backup
to a different name. Then from the current database import that column in?
I remember doing this a while ago.
Thanks in advance.
tntSounds like SQL injection. Look through the web server logs and you might be
able to see the attacks. Google it and you will get alot of info. Yes, you
can restore to a diferrent name and then update.
Jason Massie
www: http://statisticsio.com
rss: http://statisticsio.com/Home/tabid/36/rssid/1/Default.aspx
"tnt" <tnt@.discussions.microsoft.com> wrote in message
news:0624B538-B0D5-47AA-BF4F-55F13E290031@.microsoft.com...
> Hello,
> Our db has been compromised. A column in the table has been changed to
> something unwarranted for. I need suggestions on how to track this or at
> least give me some clues on how these bastard made into our database.
> Besides looking at the event logs on windows (security & system) and sql
> logs in SQL Enterprise Manager, what/where else can I look at? At this
> point
> what can i do to prevent this from happening again?
> We are on sql 2000 sp4.
> Also, one of our collegues went ahead and remove that column so it won't
> display it in the website.
> Besides restoring the whole database, can I restore the database from
> backup
> to a different name. Then from the current database import that column
> in?
> I remember doing this a while ago.
> Thanks in advance.
> tnt|||Hi,
As you are having a web application you are most likely been hit by
SQL injection.
This is very common attack technique. If you are running your web
application with a high privileged windows or sql server account (like
sa). Always use a low privileged sql-account for your web
applications. Remember that 'sa' can run commands from dos-prompt
through your web application.
In addition to windows and SQL server logs, go also through your IIS-
logs. If you have an IDS/IPS/Firewall see if you can find anything in
their logs as well. If you have some kind of logging in your web
application see if there is anything there. My experience is that
trying to track down the attacker can be very very difficult. Use your
logs to understand the how the attacker attacked your application.
If your application has poor input validation routines on input from
users you are most probably vulnerable.
The absolute best way (and probably the only way which really works)
is to use prepared statements (or parameterized statements in .net).
Remember that SQL-injection, although very dangerous, is only one of
many attack-vectors to web applications.
Hope this helps.
Best regards,
Eirik
tnt skrev:
> Hello,
> Our db has been compromised. A column in the table has been changed to
> something unwarranted for. I need suggestions on how to track this or at
> least give me some clues on how these bastard made into our database.
> Besides looking at the event logs on windows (security & system) and sql
> logs in SQL Enterprise Manager, what/where else can I look at? At this point
> what can i do to prevent this from happening again?
> We are on sql 2000 sp4.
> Also, one of our collegues went ahead and remove that column so it won't
> display it in the website.
> Besides restoring the whole database, can I restore the database from backup
> to a different name. Then from the current database import that column in?
> I remember doing this a while ago.
> Thanks in advance.
> tnt|||Thanks guys. I talked to the programmer over the phone and he had modified
the program codes.
Any inputs or instructions on how to restore a column to a database without
restoring the whole database?
Tony
"Eiriken" wrote:
> Hi,
> As you are having a web application you are most likely been hit by
> SQL injection.
> This is very common attack technique. If you are running your web
> application with a high privileged windows or sql server account (like
> sa). Always use a low privileged sql-account for your web
> applications. Remember that 'sa' can run commands from dos-prompt
> through your web application.
> In addition to windows and SQL server logs, go also through your IIS-
> logs. If you have an IDS/IPS/Firewall see if you can find anything in
> their logs as well. If you have some kind of logging in your web
> application see if there is anything there. My experience is that
> trying to track down the attacker can be very very difficult. Use your
> logs to understand the how the attacker attacked your application.
>
> If your application has poor input validation routines on input from
> users you are most probably vulnerable.
> The absolute best way (and probably the only way which really works)
> is to use prepared statements (or parameterized statements in .net).
> Remember that SQL-injection, although very dangerous, is only one of
> many attack-vectors to web applications.
> Hope this helps.
> Best regards,
> Eirik
>
> tnt skrev:
> > Hello,
> >
> > Our db has been compromised. A column in the table has been changed to
> > something unwarranted for. I need suggestions on how to track this or at
> > least give me some clues on how these bastard made into our database.
> >
> > Besides looking at the event logs on windows (security & system) and sql
> > logs in SQL Enterprise Manager, what/where else can I look at? At this point
> > what can i do to prevent this from happening again?
> >
> > We are on sql 2000 sp4.
> >
> > Also, one of our collegues went ahead and remove that column so it won't
> > display it in the website.
> >
> > Besides restoring the whole database, can I restore the database from backup
> > to a different name. Then from the current database import that column in?
> > I remember doing this a while ago.
> >
> > Thanks in advance.
> > tnt
>

Database compromise - need help

Hello,
Our db has been compromised. A column in the table has been changed to
something unwarranted for. I need suggestions on how to track this or at
least give me some clues on how these bastard made into our database.
Besides looking at the event logs on windows (security & system) and sql
logs in SQL Enterprise Manager, what/where else can I look at? At this point
what can i do to prevent this from happening again?
We are on sql 2000 sp4.
Also, one of our collegues went ahead and remove that column so it won't
display it in the website.
Besides restoring the whole database, can I restore the database from backup
to a different name. Then from the current database import that column in?
I remember doing this a while ago.
Thanks in advance.
tnt
Sounds like SQL injection. Look through the web server logs and you might be
able to see the attacks. Google it and you will get alot of info. Yes, you
can restore to a diferrent name and then update.
Jason Massie
www: http://statisticsio.com
rss: http://statisticsio.com/Home/tabid/36/rssid/1/Default.aspx
"tnt" <tnt@.discussions.microsoft.com> wrote in message
news:0624B538-B0D5-47AA-BF4F-55F13E290031@.microsoft.com...
> Hello,
> Our db has been compromised. A column in the table has been changed to
> something unwarranted for. I need suggestions on how to track this or at
> least give me some clues on how these bastard made into our database.
> Besides looking at the event logs on windows (security & system) and sql
> logs in SQL Enterprise Manager, what/where else can I look at? At this
> point
> what can i do to prevent this from happening again?
> We are on sql 2000 sp4.
> Also, one of our collegues went ahead and remove that column so it won't
> display it in the website.
> Besides restoring the whole database, can I restore the database from
> backup
> to a different name. Then from the current database import that column
> in?
> I remember doing this a while ago.
> Thanks in advance.
> tnt
|||Hi,
As you are having a web application you are most likely been hit by
SQL injection.
This is very common attack technique. If you are running your web
application with a high privileged windows or sql server account (like
sa). Always use a low privileged sql-account for your web
applications. Remember that 'sa' can run commands from dos-prompt
through your web application.
In addition to windows and SQL server logs, go also through your IIS-
logs. If you have an IDS/IPS/Firewall see if you can find anything in
their logs as well. If you have some kind of logging in your web
application see if there is anything there. My experience is that
trying to track down the attacker can be very very difficult. Use your
logs to understand the how the attacker attacked your application.
If your application has poor input validation routines on input from
users you are most probably vulnerable.
The absolute best way (and probably the only way which really works)
is to use prepared statements (or parameterized statements in .net).
Remember that SQL-injection, although very dangerous, is only one of
many attack-vectors to web applications.
Hope this helps.
Best regards,
Eirik
tnt skrev:
> Hello,
> Our db has been compromised. A column in the table has been changed to
> something unwarranted for. I need suggestions on how to track this or at
> least give me some clues on how these bastard made into our database.
> Besides looking at the event logs on windows (security & system) and sql
> logs in SQL Enterprise Manager, what/where else can I look at? At this point
> what can i do to prevent this from happening again?
> We are on sql 2000 sp4.
> Also, one of our collegues went ahead and remove that column so it won't
> display it in the website.
> Besides restoring the whole database, can I restore the database from backup
> to a different name. Then from the current database import that column in?
> I remember doing this a while ago.
> Thanks in advance.
> tnt
|||Thanks guys. I talked to the programmer over the phone and he had modified
the program codes.
Any inputs or instructions on how to restore a column to a database without
restoring the whole database?
Tony
"Eiriken" wrote:

> Hi,
> As you are having a web application you are most likely been hit by
> SQL injection.
> This is very common attack technique. If you are running your web
> application with a high privileged windows or sql server account (like
> sa). Always use a low privileged sql-account for your web
> applications. Remember that 'sa' can run commands from dos-prompt
> through your web application.
> In addition to windows and SQL server logs, go also through your IIS-
> logs. If you have an IDS/IPS/Firewall see if you can find anything in
> their logs as well. If you have some kind of logging in your web
> application see if there is anything there. My experience is that
> trying to track down the attacker can be very very difficult. Use your
> logs to understand the how the attacker attacked your application.
>
> If your application has poor input validation routines on input from
> users you are most probably vulnerable.
> The absolute best way (and probably the only way which really works)
> is to use prepared statements (or parameterized statements in .net).
> Remember that SQL-injection, although very dangerous, is only one of
> many attack-vectors to web applications.
> Hope this helps.
> Best regards,
> Eirik
>
> tnt skrev:
>
sql

Database compromise - need help

Hello,
Our db has been compromised. A column in the table has been changed to
something unwarranted for. I need suggestions on how to track this or at
least give me some clues on how these bastard made into our database.
Besides looking at the event logs on windows (security & system) and sql
logs in SQL Enterprise Manager, what/where else can I look at? At this poin
t
what can i do to prevent this from happening again?
We are on sql 2000 sp4.
Also, one of our collegues went ahead and remove that column so it won't
display it in the website.
Besides restoring the whole database, can I restore the database from backup
to a different name. Then from the current database import that column in?
I remember doing this a while ago.
Thanks in advance.
tntSounds like SQL injection. Look through the web server logs and you might be
able to see the attacks. Google it and you will get alot of info. Yes, you
can restore to a diferrent name and then update.
Jason Massie
www: http://statisticsio.com
rss: http://statisticsio.com/Home/tabid/.../1/Default.aspx
"tnt" <tnt@.discussions.microsoft.com> wrote in message
news:0624B538-B0D5-47AA-BF4F-55F13E290031@.microsoft.com...
> Hello,
> Our db has been compromised. A column in the table has been changed to
> something unwarranted for. I need suggestions on how to track this or at
> least give me some clues on how these bastard made into our database.
> Besides looking at the event logs on windows (security & system) and sql
> logs in SQL Enterprise Manager, what/where else can I look at? At this
> point
> what can i do to prevent this from happening again?
> We are on sql 2000 sp4.
> Also, one of our collegues went ahead and remove that column so it won't
> display it in the website.
> Besides restoring the whole database, can I restore the database from
> backup
> to a different name. Then from the current database import that column
> in?
> I remember doing this a while ago.
> Thanks in advance.
> tnt|||Hi,
As you are having a web application you are most likely been hit by
SQL injection.
This is very common attack technique. If you are running your web
application with a high privileged windows or sql server account (like
sa). Always use a low privileged sql-account for your web
applications. Remember that 'sa' can run commands from dos-prompt
through your web application.
In addition to windows and SQL server logs, go also through your IIS-
logs. If you have an IDS/IPS/Firewall see if you can find anything in
their logs as well. If you have some kind of logging in your web
application see if there is anything there. My experience is that
trying to track down the attacker can be very very difficult. Use your
logs to understand the how the attacker attacked your application.
If your application has poor input validation routines on input from
users you are most probably vulnerable.
The absolute best way (and probably the only way which really works)
is to use prepared statements (or parameterized statements in .net).
Remember that SQL-injection, although very dangerous, is only one of
many attack-vectors to web applications.
Hope this helps.
Best regards,
Eirik
tnt skrev:
> Hello,
> Our db has been compromised. A column in the table has been changed to
> something unwarranted for. I need suggestions on how to track this or at
> least give me some clues on how these bastard made into our database.
> Besides looking at the event logs on windows (security & system) and sql
> logs in SQL Enterprise Manager, what/where else can I look at? At this po
int
> what can i do to prevent this from happening again?
> We are on sql 2000 sp4.
> Also, one of our collegues went ahead and remove that column so it won't
> display it in the website.
> Besides restoring the whole database, can I restore the database from back
up
> to a different name. Then from the current database import that column in
?
> I remember doing this a while ago.
> Thanks in advance.
> tnt|||Thanks guys. I talked to the programmer over the phone and he had modified
the program codes.
Any inputs or instructions on how to restore a column to a database without
restoring the whole database?
Tony
"Eiriken" wrote:

> Hi,
> As you are having a web application you are most likely been hit by
> SQL injection.
> This is very common attack technique. If you are running your web
> application with a high privileged windows or sql server account (like
> sa). Always use a low privileged sql-account for your web
> applications. Remember that 'sa' can run commands from dos-prompt
> through your web application.
> In addition to windows and SQL server logs, go also through your IIS-
> logs. If you have an IDS/IPS/Firewall see if you can find anything in
> their logs as well. If you have some kind of logging in your web
> application see if there is anything there. My experience is that
> trying to track down the attacker can be very very difficult. Use your
> logs to understand the how the attacker attacked your application.
>
> If your application has poor input validation routines on input from
> users you are most probably vulnerable.
> The absolute best way (and probably the only way which really works)
> is to use prepared statements (or parameterized statements in .net).
> Remember that SQL-injection, although very dangerous, is only one of
> many attack-vectors to web applications.
> Hope this helps.
> Best regards,
> Eirik
>
> tnt skrev:
>

database cleanup

is there any SQL statement that can clear all rows in each table in a databa
se?Hi,
There is no single command to clean all the tables. You need to rite an
iterative loop traversing all the tables in the database.
For better performance try using TRUNCATE TABLE instead of DELETE
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"nonno" wrote:
> is there any SQL statement that can clear all rows in each table in a database?[/c
olor]|||Here is how you can do it.
Create a cursor on table sysobjects so that you select all the tables.
Traversing each row, create a query to TRUNCATE dynamically an then execute
the query.
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"Chandra" wrote:
> Hi,
> There is no single command to clean all the tables. You need to rite an
> iterative loop traversing all the tables in the database.
> For better performance try using TRUNCATE TABLE instead of DELETE
> --
> best Regards,
> Chandra
> http://chanduas.blogspot.com/
> http://groups.msn.com/SQLResource/
> ---
>
> "nonno" wrote:
>|||You can use the sp_msforeachtable stored procedure:
exec sp_msforeachtable 'truncate table ?'
Notice that sp_msforeachtable is a none documented stored procedure
(but it is very well known and you can find articles that describe it
with google search). Also if you have foreign keys in the DB, then
truncate can not be used for the parent tables.
Adi|||In addition , check out FKs created on the tables.
DECLARE @.TruncateStatement nvarchar(4000)
DECLARE TruncateStatements CURSOR LOCAL FAST_FORWARD
FOR
SELECT
N'TRUNCATE TABLE ' +
QUOTENAME(TABLE_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME)
FROM
INFORMATION_SCHEMA.TABLES
WHERE
TABLE_TYPE = 'BASE TABLE' AND
OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE
_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME)), 'IsMSShipped') = 0
OPEN TruncateStatements
WHILE 1 = 1
BEGIN
FETCH NEXT FROM TruncateStatements INTO @.TruncateStatement
IF @.@.FETCH_STATUS <> 0 BREAK
RAISERROR (@.TruncateStatement, 0, 1) WITH NOWAIT
EXEC(@.TruncateStatement)
END
CLOSE TruncateStatements
DEALLOCATE TruncateStatements
"Adi" <adico@.clalit.org.il> wrote in message
news:1117006995.155155.232830@.z14g2000cwz.googlegroups.com...
> You can use the sp_msforeachtable stored procedure:
> exec sp_msforeachtable 'truncate table ?'
> Notice that sp_msforeachtable is a none documented stored procedure
> (but it is very well known and you can find articles that describe it
> with google search). Also if you have foreign keys in the DB, then
> truncate can not be used for the parent tables.
> Adi
>|||http://www.extremeexperts.com/SQL/S...eAllTables.aspx
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
"nonno" <nonno@.discussions.microsoft.com> wrote in message
news:BBED9F55-36ED-4D65-9FD0-95F031F4B42F@.microsoft.com...
> is there any SQL statement that can clear all rows in each table in a
database?|||Generate a script for all the tables in the database, and then run it. This
will drop / create all tables.
"nonno" <nonno@.discussions.microsoft.com> wrote in message
news:BBED9F55-36ED-4D65-9FD0-95F031F4B42F@.microsoft.com...
> is there any SQL statement that can clear all rows in each table in a
database?sql

Database Blobs & Performance

Hi geniuses,

I have a core database question in MS Access and SQL Server.

Assume the following table in MS Access or SQL Server:

Employees table:

EmpID, Name, Hiredate, Photo;

and the following query:

"SELECT EmpID, Name, Hiredate FROM Employees;"

The question is, "does the blob object (Photo) affect the performance of the query even when not included in the select statement?"

I mean, will I gain some additional performance when separating the blob fields to another table?

Thanks for the answer.

You should ask this question in the ms - sql forums,

From my point of view it should not, and i do not have an idea of the implementation of any of the ms databases till now,

anyone has related notes please direct on this, looks interesting.

|||

if you don′t select the image / binary column there should be no performance issue.

Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Unless you have specifically created the table with the image/binary data 'in the row', it is NOT stored in the table.

sp_tableoption N'MyTable', 'large value types out of row', 'ON'

is the default. In the table, there is only a 'pointer' in the actual location.

In any case, NOT including the image/binary field in the SELECT statement will definintely enhance performance. But there will be little effect noticed by moving the image/binary pointer (16 bytes) to another table.

Database Blobs & Performance

Hi geniuses,

I have a core database question in MS Access and SQL Server.

Assume the following table in MS Access or SQL Server:

Employees table:

EmpID, Name, Hiredate, Photo;

and the following query:

"SELECT EmpID, Name, Hiredate FROM Employees;"

The question is, "does the blob object (Photo) affect the performance of the query even when not included in the select statement?"

I mean, will I gain some additional performance when separating the blob fields to another table?

Thanks for the answer.

You should ask this question in the ms - sql forums,

From my point of view it should not, and i do not have an idea of the implementation of any of the ms databases till now,

anyone has related notes please direct on this, looks interesting.

|||

if you don′t select the image / binary column there should be no performance issue.

Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Unless you have specifically created the table with the image/binary data 'in the row', it is NOT stored in the table.

sp_tableoption N'MyTable', 'large value types out of row', 'ON'

is the default. In the table, there is only a 'pointer' in the actual location.

In any case, NOT including the image/binary field in the SELECT statement will definintely enhance performance. But there will be little effect noticed by moving the image/binary pointer (16 bytes) to another table.

Database Backup for Log Shipping

Hi,
Does the Log Shipping Setup wizard provide any options to do the database
backup and restore after the initialization? what if the table structures
are changed and stored procedures are added? If not, does that mean I have
to start over the setup process again?
THanks
EdI am a little as to what you are asking as Log shipping is simply
Backup and Restore that is somewhat automated. If you add a sp then when the
backup gets applied to the other server it will create that sp there as
well. You might want to read the sections in BooksOnLIne under Log Shipping.
Andrew J. Kelly SQL MVP
"Ed" <Ed@.discussions.microsoft.com> wrote in message
news:96DCEC95-6391-4BFA-A236-D04F26D5BD1E@.microsoft.com...
> Hi,
> Does the Log Shipping Setup wizard provide any options to do the database
> backup and restore after the initialization? what if the table structures
> are changed and stored procedures are added? If not, does that mean I
> have
> to start over the setup process again?
> THanks
> Ed|||Sorry for the question.
I have the log shipping setup and everything works fine. However, the
current database structure needs to be changed, do I need to start over to
setup the log shipping again or I can just easily copy and restored the
altered database to the standby server?
"Andrew J. Kelly" wrote:

> I am a little as to what you are asking as Log shipping is simply
> Backup and Restore that is somewhat automated. If you add a sp then when t
he
> backup gets applied to the other server it will create that sp there as
> well. You might want to read the sections in BooksOnLIne under Log Shippin
g.
>
> --
> Andrew J. Kelly SQL MVP
>
> "Ed" <Ed@.discussions.microsoft.com> wrote in message
> news:96DCEC95-6391-4BFA-A236-D04F26D5BD1E@.microsoft.com...
>
>|||Hi
The log contains both DML and DDL events, so if you make a change to a SP,
or a table, or a row of data, on the source server, the change will be in th
e
log backup, and since you are using log shipping, the change will be applied
to the destination database automatically.
Get "Inside SQL Server 2000" by Kalen Delaney to get a full unserstanding
how SQL Server works internally.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Ed" wrote:
> Sorry for the question.
> I have the log shipping setup and everything works fine. However, the
> current database structure needs to be changed, do I need to start over to
> setup the log shipping again or I can just easily copy and restored the
> altered database to the standby server?
> "Andrew J. Kelly" wrote:
>|||Yes, this is the case. It is something that I don't know...
How about Trasactional Replication? I don't think the transfered Reader Log
contains all the DML and DDL, am I right?
Ed
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> The log contains both DML and DDL events, so if you make a change to a SP,
> or a table, or a row of data, on the source server, the change will be in
the
> log backup, and since you are using log shipping, the change will be appli
ed
> to the destination database automatically.
> Get "Inside SQL Server 2000" by Kalen Delaney to get a full unserstanding
> how SQL Server works internally.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
>
> "Ed" wrote:
>|||Replication and Log shipping are two different things. Replication may or
may not pass certain commands off to the subscriber but Log shipping will
make sure the other db is an exact duplicate of the first.
Andrew J. Kelly SQL MVP
"Ed" <Ed@.discussions.microsoft.com> wrote in message
news:BC2E60AA-5D35-4A7A-B992-50E7B782A267@.microsoft.com...
> Yes, this is the case. It is something that I don't know...
> How about Trasactional Replication? I don't think the transfered Reader
> Log
> contains all the DML and DDL, am I right?
> Ed
> "Mike Epprecht (SQL MVP)" wrote:
>

database audit

Does MSSQL 2000 and above have an auditing feature? We have a requirement for tracking activity and history on a particular table. I realize this can be accomplished by a simple trigger but I was wondering if MSSQL had a feature that would accomplish this.
joeTriggers can record inserts, updates, and deletes, but not reads (selects).

You either need to log this activity through stored procedures used to access the data, or turn on SQL Profiler.|||Nope, you need a trigger

http://weblogs.sqlteam.com/brettk/archive/2004/10/20/2242.aspx

Database Anomaly

Hi

I have a table call Employee, and got a field name call Employee Name

Employee Name

--, --

%%%,

(Gpsi) Dr Ac, Baruah

(Gpsi) Dr Ap, Dhital

0, 0

1 To 1, Centre

ABAYOMI, EMJ

4, US

5F4MBL01, 5F4MBL01

5Jxths01, 5Jxths01

A&E Dept(Rochdale, Zts)

NULL

ABAYASIRIWARDA, JM

Shaw, A

NULL

Zzzzzzzzzzzzzz, Aaaaaaaaaaaa

From this example data, I need to get only bolded records only, rest of the records are garbage records.

Eg: garbage Records like: “A&E Dept(Rochdale, Zts) “ has got a ‘&’ sign is not a valid employee name

“1 To 1, Centre” has got a ‘1’ is not a valid employee name

“%%%,” has got a ‘%’ sign is not a valid employee name

what is the best way to identify only valid emplyee names ?

Please help me to solve this problem

Sujithf

Sujithf:

Here is my first pass at this; I have quite a few questions:

-- --
-- Issues:
-- (1) What non alphabetic characters besides comma are allowed?
-- (2) I have assumed that a name must be formatted "lastName, firstName"
-- to be legal. Therefore, the name "Madonna" is filtered out.
-- (3) I am allowing the single quote, hyphen, period and comma characters
-- and all alphabetics. How does this list need to change?
-- (4) I set the "repeating letter" threshold at 3 repeated letters being
-- invalid.
-- (5) I put in a special condition for the 3 repeated letters condition
-- to allow "Fred Flintstone III" to pass. If this is not valid
-- this special condition test can be eliminated.
-- (6) If Roman numerals as part of the name aren't valid a test for this
-- will need to be put together.
-- (7) I have assumed that the name "Katie O'Neal" is valid.
-- (8) I have assumed that the name "Mike De La Hoz" is valid.
-- (9) I have assumed that the name [Jimmy "The Z" De Zego] is not valid.
-- --
set nocount on

declare @.names table
( employeeName varchar (30)
)
insert into @.names values ('- -, - -')
insert into @.names values ('%%%')
insert into @.names values ('(Gpsi) Dr Ac, Baruah')
insert into @.names values ('(Gpsi) Dr Ap, Dhital')
insert into @.names values ('0, 0')
insert into @.names values ('1 to 1, Centre')
insert into @.names values ('4, US')
insert into @.names values ('Abayomi, EMJ')
insert into @.names values ('5F4MBL01, 5F4MBL01')
insert into @.names values ('5Jxths01, 5Jxths01')
insert into @.names values (null)
insert into @.names values ('Abayasiriwarda, JM')
insert into @.names values (null)
insert into @.names values ('Shaw, A')
insert into @.names values ('Zzzzzzzzzzzzz, Aaaaaaaaaaaaa')
insert into @.names values ('A&E Dept(Rochdale, Zts)')

insert into @.names values ('Madonna')
insert into @.names values ('Thant, U')
insert into @.names values ('U, Betcha')
insert into @.names values ('U, U')
insert into @.names values ('Flintstone, Fred Jr')
insert into @.names values ('Flintstone, Fred III')
insert into @.names values ('Flintstone, Fred VVV')
insert into @.names values ('O''Neal, Katie')
insert into @.names values ('De La Hoz, Mike')
insert into @.names values ('Aardvark, Jimmy')
insert into @.names values ('Taylor-Curry, Ann')
insert into @.names values ('De Zego, Jimmy "The Z"')
insert into @.names values ('Suds, Dog &')
insert into @.names values ('Auto, Big 4')
insert into @.names values ('This, i''s, a-bad, name.')
--select * from @.names

select employeeName
from @.names
where employeeName like '[abcdefghijklmnopqrstuvwxyz]%, [abcdefghijklmnopqrstuvwxyz]%'
and employeeName not like '%[^abcdefghijklmnopqrstuvwxyz, ''-.]%'
and len (employeeName) = len (replace (employeeName, ',', '')) + 1
and employeeName not like '%aaa%'
and employeeName not like '%bbb%'
and employeeName not like '%ccc%'
and employeeName not like '%ddd%'
and employeeName not like '%eee%'
and employeeName not like '%fff%'
and employeeName not like '%ggg%'
and employeeName not like '%hhh%'
and employeeName not like '%iiii%'
and employeeName not like '%jjj%'
and employeeName not like '%kkk%'
and employeeName not like '%lll%'
and employeeName not like '%mmm%'
and employeeName not like '%nnn%'
and employeeName not like '%ooo%'
and employeeName not like '%ppp%'
and employeeName not like '%qqq%'
and employeeName not like '%rrr%'
and employeeName not like '%sss%'
and employeeName not like '%ttt%'
and employeeName not like '%uuu%'
and employeeName not like '%vvv%'
and employeeName not like '%www%'
and employeeName not like '%xxx%'
and employeeName not like '%yyy%'
and employeeName not like '%zzz%'

-- Sample Output:

-- employeeName
--
-- Abayomi, EMJ
-- Abayasiriwarda, JM
-- Shaw, A
-- Thant, U
-- U, Betcha
-- U, U
-- Flintstone, Fred Jr
-- Flintstone, Fred III
-- O'Neal, Katie
-- De La Hoz, Mike
-- Aardvark, Jimmy
-- Taylor-Curry, Ann

Database analysis

Hi,
Is there a tool to give me a report on all tables contained within a
microsoft DB. Looking to see total rows in each table, total size of
table.
Many Thanks
GarethOn Sep 18, 1:52 pm, payneless <gpayne_one...@.hotmail.com> wrote:
> Hi,
> Is there a tool to give me a report on all tables contained within a
> microsoft DB. Looking to see total rows in each table, total size of
> table.
> Many Thanks
> Gareth
you can try the below query
DECLARE @.Table varchar(255)
DECLARE @.NumberOfRows int
SET @.Table = 'titles'
SET @.NumberOfRows = 1
CREATE TABLE #t (name varchar(255),rows int, reserved
varchar(100),data varchar(100),index_size varchar(100), unused
varchar(100))
INSERT INTO #t
EXEC sp_MSforeachtable @.command1="sp_spaceused '?'"
SELECT * FROM #t
DROP TABLE #t
Thanks
Vijayasekar|||If you are in a SQL Server 2005 Environment and SSMS is installed then go to
a database and right click on it. Choose Reports from the popup menu and
select "Disk Usage by Table" or try other choices for alternative results.
--
Ekrem Önsoy
"payneless" <gpayne_onetel@.hotmail.com> wrote in message
news:1190105577.773630.209660@.50g2000hsm.googlegroups.com...
> Hi,
> Is there a tool to give me a report on all tables contained within a
> microsoft DB. Looking to see total rows in each table, total size of
> table.
> Many Thanks
> Gareth
>|||If you are in SQL Server install SQL Server 2005 Performance Dashboard
Reports
http://www.microsoft.com/downloads/details.aspx?FamilyID=1d3a4a0d-7e0c-4730-8204-e419218c1efc&DisplayLang=en
"payneless" <gpayne_onetel@.hotmail.com> wrote in message
news:1190105577.773630.209660@.50g2000hsm.googlegroups.com...
> Hi,
> Is there a tool to give me a report on all tables contained within a
> microsoft DB. Looking to see total rows in each table, total size of
> table.
> Many Thanks
> Gareth
>

database analysis

hello!

i'm looking for resources on database analysis. my main questions are:
how does one determine the primary key of a table, and, how does one
determine the relationships between the tables?

if you know of any tips, books or websites, i'd love to know. my
current skills for such quests are mainly looking at field names and
data and trying to figure out visio. if there are better approaches,
i'd love to learn.

thanks,

-jOn 26 Oct 2006 12:58:04 -0700, joeyp wrote:

Quote:

Originally Posted by

>hello!
>
>i'm looking for resources on database analysis. my main questions are:
>how does one determine the primary key of a table, and, how does one
>determine the relationships between the tables?
>
>if you know of any tips, books or websites, i'd love to know. my
>current skills for such quests are mainly looking at field names and
>data and trying to figure out visio. if there are better approaches,
>i'd love to learn.
>
>thanks,
>
>-j

Hi j,

If you're using SQL Server 2005, then search Books Online for "Object
Catalog Views", or check out the various INFORMATION_SCHEMA views.

For SQL Server 2000, check out the INFORMATION_SCHEMA views or the
system tables, also in Books Online.

--
Hugo Kornelis, SQL Server MVP

Database Access Control

VB.Net / SQL 2K
We are developing a VB.Net application and the question is regarding the
Login screen
We have a table which stores the access-level for each users.
Here is our requirement.
1> Need to restrict users with readonly access when connected to the
database NOT thru the application.
2> Users will gain proper access after logging into the application.
3> Once the user close the application, access-level back to 'read-only'
The whole point is to restrict users not to directly modify data outside
application.
Thanks
John B
Check out Application Roles
(http://msdn.microsoft.com/library/de...urity_89ir.asp).
Basically, create one or more app roles in the database with the
different SQL permissions necessary granted to each one (eg. you may
have a standard user role and a special admin role). When a user
authenticates through your VB app, change the permissions of the SQL
client connection with sp_setapprole (the SPID gets a whole new set of
permissions associated with the app role that completely overrides the
DB user's permissions). The new set of permissions will remain in force
until the client connection drops out (ie. disconnects).
So you can just have a normal DB role, in which all DB users are
members, that has very limited permissions (something similar to
db_datareader + db_denydatawriter) and one or more application roles in
the DB that have much less restrictive permissions (like db_datareader +
db_datawriter, but obviously more granular that that) based on the
users' access levels stored in your access-level table.
HTH
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
MS User wrote:

>VB.Net / SQL 2K
>We are developing a VB.Net application and the question is regarding the
>Login screen
>We have a table which stores the access-level for each users.
>Here is our requirement.
>1> Need to restrict users with readonly access when connected to the
>database NOT thru the application.
>2> Users will gain proper access after logging into the application.
>3> Once the user close the application, access-level back to 'read-only'
>The whole point is to restrict users not to directly modify data outside
>application.
>Thanks
>John B
>
>
>
|||You achieve this through SQL Server's built-in security. Deny all
permissions on tables and grant users execute permission only on SPs.
All data access should be through parameterized SPs so that you can
apply your own rules and ensure the user only touches what they should.
David Portas
SQL Server MVP
|||Suggest you have the application log on to the database with it's own logon,
which has read/write access. And give individual users their own logins
which have read-only access...
ALso, I strongly recommend that ALL access be allowed only through Stored
Procs, and direct access to tables be either prohibited, or restricted to
read-only, to all except for a narrrow group.
"MS User" wrote:

> VB.Net / SQL 2K
> We are developing a VB.Net application and the question is regarding the
> Login screen
> We have a table which stores the access-level for each users.
> Here is our requirement.
> 1> Need to restrict users with readonly access when connected to the
> database NOT thru the application.
> 2> Users will gain proper access after logging into the application.
> 3> Once the user close the application, access-level back to 'read-only'
> The whole point is to restrict users not to directly modify data outside
> application.
> Thanks
> John B
>
>
>
|||Unless you are in the mood to do User Security, have your AD team create
Windows Global Groups, one each for each type of access. Grant these as
logins and map them to the database.
Create user-defined database roles and put security on these roles. Create
one each for each of the Windows Global Groups above.
Now, it is a one-to-one mapping from Windows Groups to SQL Server Database
Roles.
In your case, you could just have one database role, then make that role a
member of the system defined db_datareader and db_datadenywriter roles.
The above is for ad-hoc user access only.
For coded solutions, have your application use Windows Authentication and
grant that account as an SS login, mapped to the database.
Create another database role for the application. Make the role a member of
the system defined database roles db_datadenyreader and db_datadenywriter.
Then use stored procedures exclusively. Grant the user defined role execute
rights on all the stored procedures.
Sincerely,
Anthony Thomas

"MS User" <sqlman@.sql.com> wrote in message
news:OxaNNRQJFHA.3340@.TK2MSFTNGP14.phx.gbl...
VB.Net / SQL 2K
We are developing a VB.Net application and the question is regarding the
Login screen
We have a table which stores the access-level for each users.
Here is our requirement.
1> Need to restrict users with readonly access when connected to the
database NOT thru the application.
2> Users will gain proper access after logging into the application.
3> Once the user close the application, access-level back to 'read-only'
The whole point is to restrict users not to directly modify data outside
application.
Thanks
John B

Database Access Control

VB.Net / SQL 2K
We are developing a VB.Net application and the question is regarding the
Login screen
We have a table which stores the access-level for each users.
Here is our requirement.
1> Need to restrict users with readonly access when connected to the
database NOT thru the application.
2> Users will gain proper access after logging into the application.
3> Once the user close the application, access-level back to 'read-only'
The whole point is to restrict users not to directly modify data outside
application.
Thanks
John BCheck out Application Roles
(http://msdn.microsoft.com/library/d...
rity_89ir.asp).
Basically, create one or more app roles in the database with the
different SQL permissions necessary granted to each one (eg. you may
have a standard user role and a special admin role). When a user
authenticates through your VB app, change the permissions of the SQL
client connection with sp_setapprole (the SPID gets a whole new set of
permissions associated with the app role that completely overrides the
DB user's permissions). The new set of permissions will remain in force
until the client connection drops out (ie. disconnects).
So you can just have a normal DB role, in which all DB users are
members, that has very limited permissions (something similar to
db_datareader + db_denydatawriter) and one or more application roles in
the DB that have much less restrictive permissions (like db_datareader +
db_datawriter, but obviously more granular that that) based on the
users' access levels stored in your access-level table.
HTH
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
MS User wrote:

>VB.Net / SQL 2K
>We are developing a VB.Net application and the question is regarding the
>Login screen
>We have a table which stores the access-level for each users.
>Here is our requirement.
>1> Need to restrict users with readonly access when connected to the
>database NOT thru the application.
>2> Users will gain proper access after logging into the application.
>3> Once the user close the application, access-level back to 'read-only'
>The whole point is to restrict users not to directly modify data outside
>application.
>Thanks
>John B
>
>
>|||You achieve this through SQL Server's built-in security. Deny all
permissions on tables and grant users execute permission only on SPs.
All data access should be through parameterized SPs so that you can
apply your own rules and ensure the user only touches what they should.
David Portas
SQL Server MVP
--|||Suggest you have the application log on to the database with it's own logon,
which has read/write access. And give individual users their own logins
which have read-only access...
ALso, I strongly recommend that ALL access be allowed only through Stored
Procs, and direct access to tables be either prohibited, or restricted to
read-only, to all except for a narrrow group.
"MS User" wrote:

> VB.Net / SQL 2K
> We are developing a VB.Net application and the question is regarding the
> Login screen
> We have a table which stores the access-level for each users.
> Here is our requirement.
> 1> Need to restrict users with readonly access when connected to the
> database NOT thru the application.
> 2> Users will gain proper access after logging into the application.
> 3> Once the user close the application, access-level back to 'read-only'
> The whole point is to restrict users not to directly modify data outside
> application.
> Thanks
> John B
>
>
>|||Unless you are in the mood to do User Security, have your AD team create
Windows Global Groups, one each for each type of access. Grant these as
logins and map them to the database.
Create user-defined database roles and put security on these roles. Create
one each for each of the Windows Global Groups above.
Now, it is a one-to-one mapping from Windows Groups to SQL Server Database
Roles.
In your case, you could just have one database role, then make that role a
member of the system defined db_datareader and db_datadenywriter roles.
The above is for ad-hoc user access only.
For coded solutions, have your application use Windows Authentication and
grant that account as an SS login, mapped to the database.
Create another database role for the application. Make the role a member of
the system defined database roles db_datadenyreader and db_datadenywriter.
Then use stored procedures exclusively. Grant the user defined role execute
rights on all the stored procedures.
Sincerely,
Anthony Thomas
"MS User" <sqlman@.sql.com> wrote in message
news:OxaNNRQJFHA.3340@.TK2MSFTNGP14.phx.gbl...
VB.Net / SQL 2K
We are developing a VB.Net application and the question is regarding the
Login screen
We have a table which stores the access-level for each users.
Here is our requirement.
1> Need to restrict users with readonly access when connected to the
database NOT thru the application.
2> Users will gain proper access after logging into the application.
3> Once the user close the application, access-level back to 'read-only'
The whole point is to restrict users not to directly modify data outside
application.
Thanks
John B

Database Access Control

VB.Net / SQL 2K
We are developing a VB.Net application and the question is regarding the
Login screen
We have a table which stores the access-level for each users.
Here is our requirement.
1> Need to restrict users with readonly access when connected to the
database NOT thru the application.
2> Users will gain proper access after logging into the application.
3> Once the user close the application, access-level back to 'read-only'
The whole point is to restrict users not to directly modify data outside
application.
Thanks
John BThis is a multi-part message in MIME format.
--030706090505000407000501
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Check out Application Roles
(http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_security_89ir.asp).
Basically, create one or more app roles in the database with the
different SQL permissions necessary granted to each one (eg. you may
have a standard user role and a special admin role). When a user
authenticates through your VB app, change the permissions of the SQL
client connection with sp_setapprole (the SPID gets a whole new set of
permissions associated with the app role that completely overrides the
DB user's permissions). The new set of permissions will remain in force
until the client connection drops out (ie. disconnects).
So you can just have a normal DB role, in which all DB users are
members, that has very limited permissions (something similar to
db_datareader + db_denydatawriter) and one or more application roles in
the DB that have much less restrictive permissions (like db_datareader +
db_datawriter, but obviously more granular that that) based on the
users' access levels stored in your access-level table.
HTH
--
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
MS User wrote:
>VB.Net / SQL 2K
>We are developing a VB.Net application and the question is regarding the
>Login screen
>We have a table which stores the access-level for each users.
>Here is our requirement.
>1> Need to restrict users with readonly access when connected to the
>database NOT thru the application.
>2> Users will gain proper access after logging into the application.
>3> Once the user close the application, access-level back to 'read-only'
>The whole point is to restrict users not to directly modify data outside
>application.
>Thanks
>John B
>
>
>
--030706090505000407000501
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>Check out Application Roles (<a
href="http://links.10026.com/?link=http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_security_89ir.asp</a>).<br>">http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_security_89ir.asp">http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_security_89ir.asp</a>).<br>
<br>
Basically, create one or more app roles in the database with the
different SQL permissions necessary granted to each one (eg. you may
have a standard user role and a special admin role). When a user
authenticates through your VB app, change the permissions of the SQL
client connection with sp_setapprole (the SPID gets a whole new set of
permissions associated with the app role that completely overrides the
DB user's permissions). The new set of permissions will remain in
force until the client connection drops out (ie. disconnects).<br>
<br>
So you can just have a normal DB role, in which all DB users are
members, that has very limited permissions (something similar to
db_datareader + db_denydatawriter) and one or more application roles in
the DB that have much less restrictive permissions (like db_datareader
+ db_datawriter, but obviously more granular that that) based on the
users' access levels stored in your access-level table.<br>
<br>
HTH<br>
</tt>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
<p><span lang="en-au"><font face="Tahoma" size="2">--<br>
</font> </span><b><span lang="en-au"><font face="Tahoma" size="2">mike
hodgson</font></span></b><span lang="en-au"> <font face="Tahoma"
size="2">|</font><i><font face="Tahoma"> </font><font face="Tahoma"
size="2"> database administrator</font></i><font face="Tahoma" size="2">
| mallesons</font><font face="Tahoma"> </font><font face="Tahoma"
size="2">stephen</font><font face="Tahoma"> </font><font face="Tahoma"
size="2"> jaques</font><font face="Tahoma"><br>
</font><b><font face="Tahoma" size="2">T</font></b><font face="Tahoma"
size="2"> +61 (2) 9296 3668 |</font><b><font face="Tahoma"> </font><font
face="Tahoma" size="2"> F</font></b><font face="Tahoma" size="2"> +61
(2) 9296 3885 |</font><b><font face="Tahoma"> </font><font
face="Tahoma" size="2">M</font></b><font face="Tahoma" size="2"> +61
(408) 675 907</font><br>
<b><font face="Tahoma" size="2">E</font></b><font face="Tahoma" size="2">
<a href="http://links.10026.com/?link=mailto:mike.hodgson@.mallesons.nospam.com">mailto:mike.hodgson@.mallesons.nospam.com</a>
|</font><b><font face="Tahoma"> </font><font face="Tahoma" size="2">W</font></b><font
face="Tahoma" size="2"> <a href="http://links.10026.com/?link=/">http://www.mallesons.com">
http://www.mallesons.com</a></font></span> </p>
</div>
<br>
<br>
MS User wrote:
<blockquote cite="midOxaNNRQJFHA.3340@.TK2MSFTNGP14.phx.gbl" type="cite">
<pre wrap="">VB.Net / SQL 2K
We are developing a VB.Net application and the question is regarding the
Login screen
We have a table which stores the access-level for each users.
Here is our requirement.
1> Need to restrict users with readonly access when connected to the
database NOT thru the application.
2> Users will gain proper access after logging into the application.
3> Once the user close the application, access-level back to 'read-only'
The whole point is to restrict users not to directly modify data outside
application.
Thanks
John B
</pre>
</blockquote>
</body>
</html>
--030706090505000407000501--|||You achieve this through SQL Server's built-in security. Deny all
permissions on tables and grant users execute permission only on SPs.
All data access should be through parameterized SPs so that you can
apply your own rules and ensure the user only touches what they should.
--
David Portas
SQL Server MVP
--|||Suggest you have the application log on to the database with it's own logon,
which has read/write access. And give individual users their own logins
which have read-only access...
ALso, I strongly recommend that ALL access be allowed only through Stored
Procs, and direct access to tables be either prohibited, or restricted to
read-only, to all except for a narrrow group.
"MS User" wrote:
> VB.Net / SQL 2K
> We are developing a VB.Net application and the question is regarding the
> Login screen
> We have a table which stores the access-level for each users.
> Here is our requirement.
> 1> Need to restrict users with readonly access when connected to the
> database NOT thru the application.
> 2> Users will gain proper access after logging into the application.
> 3> Once the user close the application, access-level back to 'read-only'
> The whole point is to restrict users not to directly modify data outside
> application.
> Thanks
> John B
>
>
>|||Unless you are in the mood to do User Security, have your AD team create
Windows Global Groups, one each for each type of access. Grant these as
logins and map them to the database.
Create user-defined database roles and put security on these roles. Create
one each for each of the Windows Global Groups above.
Now, it is a one-to-one mapping from Windows Groups to SQL Server Database
Roles.
In your case, you could just have one database role, then make that role a
member of the system defined db_datareader and db_datadenywriter roles.
The above is for ad-hoc user access only.
For coded solutions, have your application use Windows Authentication and
grant that account as an SS login, mapped to the database.
Create another database role for the application. Make the role a member of
the system defined database roles db_datadenyreader and db_datadenywriter.
Then use stored procedures exclusively. Grant the user defined role execute
rights on all the stored procedures.
Sincerely,
Anthony Thomas
"MS User" <sqlman@.sql.com> wrote in message
news:OxaNNRQJFHA.3340@.TK2MSFTNGP14.phx.gbl...
VB.Net / SQL 2K
We are developing a VB.Net application and the question is regarding the
Login screen
We have a table which stores the access-level for each users.
Here is our requirement.
1> Need to restrict users with readonly access when connected to the
database NOT thru the application.
2> Users will gain proper access after logging into the application.
3> Once the user close the application, access-level back to 'read-only'
The whole point is to restrict users not to directly modify data outside
application.
Thanks
John B

Database Access Control

VB.Net / SQL 2K
We are developing a VB.Net application and the question is regarding the
Login screen
We have a table which stores the access-level for each users.
Here is our requirement.
1> Need to restrict users with readonly access when connected to the
database NOT thru the application.
2> Users will gain proper access after logging into the application.
3> Once the user close the application, access-level back to 'read-only'
The whole point is to restrict users not to directly modify data outside
application.
Thanks
John BCheck out Application Roles
(http://msdn.microsoft.com/library/d...
rity_89ir.asp).
Basically, create one or more app roles in the database with the
different SQL permissions necessary granted to each one (eg. you may
have a standard user role and a special admin role). When a user
authenticates through your VB app, change the permissions of the SQL
client connection with sp_setapprole (the SPID gets a whole new set of
permissions associated with the app role that completely overrides the
DB user's permissions). The new set of permissions will remain in force
until the client connection drops out (ie. disconnects).
So you can just have a normal DB role, in which all DB users are
members, that has very limited permissions (something similar to
db_datareader + db_denydatawriter) and one or more application roles in
the DB that have much less restrictive permissions (like db_datareader +
db_datawriter, but obviously more granular that that) based on the
users' access levels stored in your access-level table.
HTH
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
MS User wrote:

>VB.Net / SQL 2K
>We are developing a VB.Net application and the question is regarding the
>Login screen
>We have a table which stores the access-level for each users.
>Here is our requirement.
>1> Need to restrict users with readonly access when connected to the
>database NOT thru the application.
>2> Users will gain proper access after logging into the application.
>3> Once the user close the application, access-level back to 'read-only'
>The whole point is to restrict users not to directly modify data outside
>application.
>Thanks
>John B
>
>
>|||You achieve this through SQL Server's built-in security. Deny all
permissions on tables and grant users execute permission only on SPs.
All data access should be through parameterized SPs so that you can
apply your own rules and ensure the user only touches what they should.
David Portas
SQL Server MVP
--|||Suggest you have the application log on to the database with it's own logon,
which has read/write access. And give individual users their own logins
which have read-only access...
ALso, I strongly recommend that ALL access be allowed only through Stored
Procs, and direct access to tables be either prohibited, or restricted to
read-only, to all except for a narrrow group.
"MS User" wrote:

> VB.Net / SQL 2K
> We are developing a VB.Net application and the question is regarding the
> Login screen
> We have a table which stores the access-level for each users.
> Here is our requirement.
> 1> Need to restrict users with readonly access when connected to the
> database NOT thru the application.
> 2> Users will gain proper access after logging into the application.
> 3> Once the user close the application, access-level back to 'read-only'
> The whole point is to restrict users not to directly modify data outside
> application.
> Thanks
> John B
>
>
>|||Unless you are in the mood to do User Security, have your AD team create
Windows Global Groups, one each for each type of access. Grant these as
logins and map them to the database.
Create user-defined database roles and put security on these roles. Create
one each for each of the Windows Global Groups above.
Now, it is a one-to-one mapping from Windows Groups to SQL Server Database
Roles.
In your case, you could just have one database role, then make that role a
member of the system defined db_datareader and db_datadenywriter roles.
The above is for ad-hoc user access only.
For coded solutions, have your application use Windows Authentication and
grant that account as an SS login, mapped to the database.
Create another database role for the application. Make the role a member of
the system defined database roles db_datadenyreader and db_datadenywriter.
Then use stored procedures exclusively. Grant the user defined role execute
rights on all the stored procedures.
Sincerely,
Anthony Thomas
"MS User" <sqlman@.sql.com> wrote in message
news:OxaNNRQJFHA.3340@.TK2MSFTNGP14.phx.gbl...
VB.Net / SQL 2K
We are developing a VB.Net application and the question is regarding the
Login screen
We have a table which stores the access-level for each users.
Here is our requirement.
1> Need to restrict users with readonly access when connected to the
database NOT thru the application.
2> Users will gain proper access after logging into the application.
3> Once the user close the application, access-level back to 'read-only'
The whole point is to restrict users not to directly modify data outside
application.
Thanks
John B

database access & asp.net

If a user has permissions to add and delete rows from a table i.e. adding
and removing items from an order what is to stop a malicious user changing
the product code in the form and then adding to or removing items to/from
another user's order ?
How do we ensure that the rows the user is editing are rows the user has
permission to edit ?
ThanksTwo words Application Architecture. Seriously you need to implement your
own security model if you want to provide for row level and field level
security.
Secondly if you are developing a web app the user should not have rights to
your database. The connection should be handled by an "Application User"
that in turn is managed by a Connection Pool.
Dan
"Murphy" <murphy@.murphy.com> wrote in message
news:%23wb%23$V%23oDHA.684@.TK2MSFTNGP09.phx.gbl...
> If a user has permissions to add and delete rows from a table i.e. adding
> and removing items from an order what is to stop a malicious user changing
> the product code in the form and then adding to or removing items to/from
> another user's order ?
> How do we ensure that the rows the user is editing are rows the user has
> permission to edit ?
> Thanks
>|||Are there any security models that have been tried and proven, don't want to
reinvent the wheel ?
Thanks
"solex" <solex@.nowhere.com> wrote in message
news:%23oTqDa%23oDHA.708@.TK2MSFTNGP10.phx.gbl...
> Two words Application Architecture. Seriously you need to implement your
> own security model if you want to provide for row level and field level
> security.
> Secondly if you are developing a web app the user should not have rights
to
> your database. The connection should be handled by an "Application User"
> that in turn is managed by a Connection Pool.
> Dan
> "Murphy" <murphy@.murphy.com> wrote in message
> news:%23wb%23$V%23oDHA.684@.TK2MSFTNGP09.phx.gbl...
> > If a user has permissions to add and delete rows from a table i.e.
adding
> > and removing items from an order what is to stop a malicious user
changing
> > the product code in the form and then adding to or removing items
to/from
> > another user's order ?
> >
> > How do we ensure that the rows the user is editing are rows the user has
> > permission to edit ?
> >
> > Thanks
> >
> >
>|||There are many security models but none that I have seen that will implement
row level security in your database. It has been my experience that if you
want granular security you will need to implement it on your own.
Dan
"Murphy" <murphy@.murphy.com> wrote in message
news:eosI6c%23oDHA.2652@.TK2MSFTNGP09.phx.gbl...
> Are there any security models that have been tried and proven, don't want
to
> reinvent the wheel ?
> Thanks
> "solex" <solex@.nowhere.com> wrote in message
> news:%23oTqDa%23oDHA.708@.TK2MSFTNGP10.phx.gbl...
> > Two words Application Architecture. Seriously you need to implement
your
> > own security model if you want to provide for row level and field level
> > security.
> >
> > Secondly if you are developing a web app the user should not have rights
> to
> > your database. The connection should be handled by an "Application
User"
> > that in turn is managed by a Connection Pool.
> >
> > Dan
> >
> > "Murphy" <murphy@.murphy.com> wrote in message
> > news:%23wb%23$V%23oDHA.684@.TK2MSFTNGP09.phx.gbl...
> > > If a user has permissions to add and delete rows from a table i.e.
> adding
> > > and removing items from an order what is to stop a malicious user
> changing
> > > the product code in the form and then adding to or removing items
> to/from
> > > another user's order ?
> > >
> > > How do we ensure that the rows the user is editing are rows the user
has
> > > permission to edit ?
> > >
> > > Thanks
> > >
> > >
> >
> >
>

dataadapter.Update() OR ExecuteNonQuery()

Hy, again! I am at the begining of an application. I have some modules that insert, delete and update only one row at one table. My question is should I use dataadapter.Update() or ExecuteNonQuery(). I prefer ExecuteNonQuery because I want to build a class : DataLayer to implement my own InsertProcedure(), UpdateProcedure(),DeleteProcedure(). I want speed in my application, so which is the best: dataadapter.Update() OR ExecuteNonQuery(). Thank you!

Hi

If you are only intending on working with one row at a time (presumably using the primary key for the data you are working with) then it would be more efficient to use the ExecuteNonQuery method of the command object. The Update method of the DataAdapter object will attempt to update all changes to rows within a DataSet/DataTable.

HTH

|||You said: "Update method of the DataAdapter object will attempt to update ALL changes to rows within a DataSet/DataTable", even if I write: dataadapter.UpdateCommand="update TableName set field1 with ? for .... "?|||

Hi

If you specify the DataAdapter's UpdateCommand to update only one row then that will be similar to the ExecuteNonQuery method. I assumed by DataAdapter.Update that you were going to pass it a DataSet/DataTable so that all changed rows would be persisted to the Database.

To be honest though, the DataAdapter's UpdateCommand is a command object so therefore, to update a row you would have configured a DataAdapter and then configured a Command object when you could simply have configured a Command object only. However, if you already had the DataAdapter available to you then this wouldn't be so much of an issue. It really depends on the type of aproach you are taking, especially when considering a Data Access Layer.

HTH

|||Yes, you are right. With dataadapter the work is double. Thank you.

DataAdapter Update Problems

Hi, I am populating a datagrid with data from one sql table, I add additional columns and then wish to write it back out to another table. I have written the following code to acheive this, however it keeps throwing the follwing exception - "Update requites a valid UpdateComman when passed a DataRow collection with modified rows"

I've looked and I can see what the problem is, can anybody help please?

Private Sub FillData()'SQL connectionMe.sqlconnection =New System.Data.SqlClient.SqlConnectionMe.sqlconnection.ConnectionString = ConfigurationSettings.AppSettings("CuroConnection")Dim sqlAs String If Request("action") ="gen"Then sql ="select item from tblPickList where GroupName ='xxx'AND Category = '" & Category2 & "' ORDER BY item ASC" ElseIf Request("action") = "fav" Then sql = "select identifiertext as item from tblfavourites where HRID =" & Session("hrid") & " and type = 3 ORDER BY identifiertext ASC" ElseIf Request("action") = "spec" Then sql = "select item from tblPickList where GroupName ='" & GroupID & "' AND Category = '" & Category2 & "' ORDER BY item ASC"End If ds =New DataSet da =New SqlClient.SqlDataAdapter(sql, sqlconnection)Try sqlconnection.Open() da.Fill(ds,"tblPickListTemp") sqlconnection.Close()Catch exAs Exception sqlconnection.Close()End Try ds.Tables(0).Columns.Add("HRID") ds.Tables(0).Columns.Add("Flag")'may possibly add a favourites column here at a later date to ensure no duplicationDim drAs DataRowFor Each drIn ds.Tables("tblPickListTemp").Rows dr("HRID") = Session("hrid") dr("Flag") = 0Next DgrdFavourites.DataSource = ds DgrdFavourites.DataBind()End Sub Public Sub CreateTempRecords()'SQL connectionMe.sqlconnection =New System.Data.SqlClient.SqlConnectionMe.sqlcommand =New System.Data.SqlClient.SqlCommandMe.da =New SqlClient.SqlDataAdapterMe.sqlconnection.ConnectionString = ConfigurationSettings.AppSettings("CuroConnection")Dim sqlAs String sql ="Insert into tblPickListTemp (Item, HRID, Flag) values (@.Item, @.HRID, @.Flag)" sqlcommand.CommandText = sql sqlcommand.Connection = sqlconnection sqlcommand.Parameters.Add("@.Item","Item") sqlcommand.Parameters.Add("@.HRID","HRID") sqlcommand.Parameters.Add("@.Flag","Flag") da.InsertCommand = sqlcommand da.Update(ds.Tables(0))End Sub

Hello Naimh,

Your program retrieves records from the database, you modify them and try to update the records in the database.

In that case you need to add an UpdateCommand to your SqlDataAdapter.

The Update statement will than handle new rows and modified rows.

Jeroen Molenaar.

|||I thought that was what I was doing with the da.InsertCommand on the 2nd to last line.I'm not sure I've really got to grips with the dataadapter syntax, could you show me please?|||

The InsertCommand you use is correct. But you are not adding new rows to the table, so you need also the UpdateCommand.

sql = "Update tblPickList Set HRID = @.HRID, Flag = @.Flag where Item = @.Item"

// Add the parameters here ...

sqlcommand.CommandText = sql

da.UpdateCommand = sqlcommand

// etc.

Hope you get it working now.

Jeroen Molenaar.

|||

jeroenm:

The InsertCommand you use is correct. But you are not adding new rows to the table, so you need also the UpdateCommand.

Okay really confused now, as that is exactly what I want to do, I populate the dataset from one table, manipulate it in the dataset and then Insert entirely new row's into a completely different table, erk! I shall give your suggestion a try but I have to fess up to being none the wiser.|||

jeroenm:

sql?= "Update tblPickListTemp Set HRID = @.HRID, Flag = @.Flag where Item = @.Item"

See now this is never going to work for me, tblPickListTemp ( the table I want to insert rows into) at the start of this process is empty, I cannot update and set fields to new values when no records currently exist.Ah well back to the drawing board, thanks anyway.|||

Do you have an AcceptChanges statement somewhere before the data is saved to the database?

When you modify the dataset after AcceptChanges, the tableadapter thinks it needs an Update statement in stead of an Insert statement.

Jeroen Molenaar.

|||No I am afraid I don't, all the code is in the snippet above. It's most confusing. I have also ensured that the primary key is set in the recipient table, which is another common problem which throws this error. I am at a real loss and almost at the point of scrapping the whole thing and taking a different approach, it's just that if I could have got this to work it would have been less trips to the Db and a lot less spaghetti code.|||

You can add some ASP.NET code. Perhaps this gives another clue.

|||

<form id="Form1" method="post" runat="server"><TABLE id="Table2" cellSpacing="0" cellPadding="0" width="280" align="center" border="0"><TR height="24"><TD class="fntmed" style="WIDTH: 280px" background="../images/header.jpg">         PickList</TD></TR><TR><TD class="fntnorm" style="WIDTH: 280px; HEIGHT: 36px"><IMG height="6" src="../images/6pixspacer.gif"><br><asp:image id="imgLine" runat="server" Width="260px" ImageUrl="../Images/line.jpg"></asp:image></TD></TR><TR><TD><div style="OVERFLOW: auto; WIDTH: 270px; POSITION: static; HEIGHT: 190px"><asp:DataGrid id="DgrdFavourites" autogeneratecolumns="False" BackColor="#ffffff" showheader="False"OnItemCommand="DgrdFavourites_ItemCommand" runat="server" Width="256px" Height="25px" BorderWidth="0"CssClass="fntsmall" AlternatingItemStyle-CssClass="fntsmall"><AlternatingItemStyle BackColor="#e2eaee" /><Columns><asp:BoundColumn DataField="Item" /><asp:templatecolumn ItemStyle-HorizontalAlign="Right"><ItemTemplate><asp:button CssClass="buttonlike" Text="Select" commandname="Select" runat="server" ID="btnSelect" /></ItemTemplate></asp:templatecolumn><asp:templatecolumn ItemStyle-HorizontalAlign="Right"><ItemTemplate><asp:button CssClass="buttonlike" Text="Favourite" runat="server" commandname="Add" ID="btnAdd" /></ItemTemplate></asp:templatecolumn></Columns></asp:DataGrid></div></TD></TR></TABLE><asp:Label ID="oops" Runat="server"></asp:Label></form></body>

That the entirety of the front end code, basically at page load inside a "If not Is Postback" the Filldata() method is invoked followed by the CreateTempData() method. Not a lot to it really.|||

Hello Naimh,

Sorry, but I've been moving you into the wrong direction. My first idea was correct, but I didn't read your code properly.

When you call FillData the dataset ds is filled from the table tblPickList. In FillData you add 2 columns and modify all rows with the session HRID. At this stage your rows have the status Modified in the dataset ds. (This generates the error message when calling da.Update())

Before "DgrdFavourites.DataSource = ds" you can enter the command ds.AcceptChanges(). All modifications are accepted and no update statement will be generated by the dataadapter.

The second thing is make some changes to CreateTempRecords. Create a new dataset dstemp as a copy of ds. After that insert the rows from ds into the new dataset dstemp, for example with a foreach construction. Now the new dataset is ready for the insert statement that you have written in CreateTempRecords(). Change da.Update(ds.Tables(0)) into da.Update(dstemp.Tables(0)).

Now your code should do the job!

Jeroen Molenaar.

Data/Table Last Accessed/Modified

Is anyone aware of a method of determining when a table was last modified or accessed?

Some of our databases have tables that I am sure are not being used and I would like to generate a list of tables that have not been accessed or modified for some period of time.

I looked for a system procedure but didn't see anything that satisfied my need.

Currently I rename suspect tables and wait for someone or some process to gripe, but I don't care for that method for obvious reasons.

Thanks!again .. r u talking about the data or the object?
check what brett says here:
http://www.dbforums.com/t995468.html|||Both actually. I would like to know when someone queries against it or when the data is modified. The trigger method is good for data mods...I can use something similar. But it might be hard see if a table has been accessed or read.

Thanks!|||The only effective way I know to do that is using SQL Profiler (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_mon_perf_86ib.asp). Be forwarned: This can use enormous amounts of disk if you don't set it up very carefully!

This is when I wish that the design team had left the SELECT triggers in place, although that could have been a nightmare too!

-PatP|||That may do the trick. Thanks!

Data XML file to SQL Server Table...

Hi,

I have the following XML document, which I want to put into SQL Server 2000 Table. How can I do that? The table name is "Orders". I am using C#.

<Order>

<Date>2007/09/02</Date>

<OrderID>200</OrderID>

<PartNumber id = “ABC10”>

<Delivery_Date>

</Delivery Date>

<Delivery_Quanity>

</Delivery_Quanity>

<Price>

</Price>

</PartNumber>

<PartNumber id = “ABC98”>

<Delivery_Date>

</Delivery Date>

<Delivery_Quanity>

</Delivery_Quanity>

<Price>

</Price>

</PartNumber>

<PartNumber id = “ABC11”>

<Delivery_Date>

</Delivery Date>

<Delivery_Quanity>

</Delivery_Quanity>

<Price>

</Price>

</PartNumber>

</Order>

Any help would be highly appreciated...

Regards,

Hi again,

Just to explain my question again,

I want the table to look like the following,

Orders:

Auto: Date: OrderID: Partnumber: Delivery_Date: Delivery_Quantity: Price:

0001 2007/09/02 200 ABC10 0 1 0

0002 2007/09/02 200 ABC10 0 2 0

0003 2007/09/02 200 ABC98 0 56 0

0004 2007/09/02 200 ABC11 0 34 0

Orders.XML

<Order>

<Date>2007/09/02</Date>

<OrderID>200</OrderID>

<PartNumber id = “ABC10”>

<Delivery_Date> <!--Can have multiple Delivery_Date, Delivery_Quantity and Price-->

</Delivery Date>

<Delivery_Quanity>

1

</Delivery_Quanity>

<Price>

</Price>

<Delivery_Date> <!--Can have multiple Delivery_Date, Delivery_Quantity and Price-->

</Delivery Date>

<Delivery_Quanity>

2

</Delivery_Quanity>

<Price>

</Price>

</PartNumber>

<PartNumber id = “ABC98”>

<Delivery_Date>

</Delivery Date>

<Delivery_Quanity>

56

</Delivery_Quanity>

<Price>

</Price>

</PartNumber>

<PartNumber id = “ABC11”>

<Delivery_Date>

</Delivery Date>

<Delivery_Quanity>

34

</Delivery_Quanity>

<Price>

</Price>

</PartNumber>

</Order>

Regards,