Help Im a Noob - Updating a database entry.

Ok, so I work with a certain program which has a wide open back door into it's data tables. As a program administrator i can go into the actual program and change details of other users etc. However here's what i need to do...

A certain users security level needs to be reduced from administrator to a lower level. If i do that within the program it will automatically require that i change that users password, which i don't know.

The user is not to know that their access level has changed.

Backdooring the database through MSQuery and hunting online a little i found how to update information...

UPDATE User
SET Security = 4
WHERE UserID = 4

This works fine, however the users password is then scrambled, until an administrator edits their password for them.

The password itself is encrypted within the database.
Appearing something like "99171821E244877B4201003E0519EEA7"
It appears to be hexadecimal, but hey, i'm no system engineer. Strange thing is. that encryption represents the password "bill". When i execute the change on security level, the password "bill" becomes invalid, yet the encrypted string remains exactly the same.

What i need if at all possible is a how to... on forcing the database to not change the password field when the security level is updated. Any other suggestions would be most welcome. And of course plain english with syntax explanations would be nice as i am very much a junior, this has been my first SQL code execution, aside from queries.

Cheers,
OostersUnfortunately for you, this seems to be an application issue.

If it were a DBMS problem, then I would assume you are not using ORACLE RDBMS; such problems only occur in mediocre DBMS like SQL Server and others.
:eek:|||Application problems seem pretty indiscriminant about what engines they affect to me. If an app uses SQL, and that SQL breaks, it breaks for any database that implements SQL at the same level. If an app uses database engine specific code (Oracle, Microsoft, DB2, etc), then that app will probably only run on its target database engine, but that is a failure in the application, not the database.

I think that the problem that Oosters has found is that multiple columns within the users table are used to derive (actually decrypt) the password. It would appear that the application designers wanted to prevent exactly the kind of change that Oosters is trying to make.

-PatP|||Unfortunately for you, this seems to be an application issue.

Indeed.

If it were a DBMS problem, then I would assume you are not using ORACLE RDBMS; such problems only occur in mediocre DBMS like SQL Server and others.
:eek:

And yet you felt it was imperative to squeeze that into your post regardless.

Quality.

Would you pretty pretty please proceed to regal me with unsolicited opinions and stories about how linux cured your stepdad's brother's cousin's dogs' nephew's sister's case of terminal cancer, or perhaps the one about Microsoft releasing OS's laced with mind control algorhythms that only appear at a particular resolution and refresh rate?|||In the midst of these response all i basically understood, was that the application designers didn't want me to do it. Why couldn't they just stop me backdooring the application in the first place.

Meanwhile I think i know what's happening with the password. I think instead of the database automatically changing the password when i update the security level of a user, there may be another field to lock the password when i update security level, and this will only unlock when another password is offered. I can't even get around it by feeding the database it's own encryption string, which should force it to recognise a password change. I'm off to hunt for a password locking field.

I could destroy the integrity of the entire database at the touch of a button, but i can't change a single users security level.|||In the midst of these response all i basically understood, was that the application designers didn't want me to do it. Why couldn't they just stop me backdooring the application in the first place.

Because controlling access to your database server probably isn't their job. Who is going to keep the database running if everybody is completely locked out of it?|||One of the really fundamental elements of good security design is to limit the damage that can be done without detection. A fundamental way to achieve this is to require some form of intervention from one or more of the affected parties whenever a change is made. The example you've got is when a security level is changed, make that change require a password change.

This has little to do with the database. It is a fundamental requisite for good security management.

While I'm sure that it frustrates you, I see it as a good thing in the larger context because it makes an otherwise unannounced change visible to the affected party. While it might not be politically expedient, you shouldn't be able (from a systemic point of view) to take away (or grant) administrative privledges without the knowledge of the user.

-PatP|||Yes, In an ideal world, in an ideal database i shouldn't be able to change user security levels without their knowledge. This being said...

<RANT>
Considering the sensitivity of information as this is a financial database representing the business of the whole company, I shouldn't be permitted to backdoor and alter financial information, and yet as long as i make it balance, which can take a bit of figuring, the database security allows such changes.
</RANT>

No need to respond to this, thanks for the knowledge people.

regards,
Matt

Help for a new user

I've created a DB in SQL Server, and a user 'X', administrator on it. The DB
is genereted by a file, using osql. The tables' owner is 'X', but when I
try to insert some rows into the tables (by osql), the response is:
"INSERT permission denied on object 'DUMMY', database 'EC_PROVA', owner
'X' "

Is there anybody that can help me?
Thank you.
Fedefede (fedina_no_chicca_spam@.libero_spam_no.it) writes:
> I've created a DB in SQL Server, and a user 'X', administrator on it.
> The DB is genereted by a file, using osql. The tables' owner is 'X',
> but when I try to insert some rows into the tables (by osql), the
> response is: "INSERT permission denied on object 'DUMMY', database
> 'EC_PROVA', owner 'X' "

As whom do you run the INSERT statement?

What does "sp_helprotect DUMMY" say?

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||"Erland Sommarskog" <esquel@.sommarskog.se> ha scritto nel messaggio
> As whom do you run the INSERT statement?
I launch a script using this command line:
osql -U X -P passX -D SQLPROVA -i "Schema20040723.sql" -o "SchemaCrea.log"
So, I think the user is X...
> What does "sp_helprotect DUMMY" say?
It doesn't say anything...|||fede (fedina_no_chicca_spam@.libero_spam_no.it) writes:
> "Erland Sommarskog" <esquel@.sommarskog.se> ha scritto nel messaggio
>> As whom do you run the INSERT statement?
> I launch a script using this command line:
> osql -U X -P passX -D SQLPROVA -i "Schema20040723.sql" -o "SchemaCrea.log"
> So, I think the user is X...

But I don't what it's that script. It is difficult to assist when I don't
have full information of what you are doing. If that script performs

CREATE TABLE dummy (a int NOT NULL)
go
INSERT dummy (a) VALUES(12)

This should succeed. So I assume you are doing something else, but I don't
know what.

>> What does "sp_helprotect DUMMY" say?
> It doesn't say anything...

Not even?

Server: Msg 15330, Level 11, State 1, Procedure sp_helprotect, Line 346
There are no matching rows on which to report.

By the way, what does SELECT @.@.version say on your server?

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||"Erland Sommarskog" <esquel@.sommarskog.se> ha scritto nel messaggio
news:Xns953279F885DYazorman@.127.0.0.1...

> But I don't what it's that script. It is difficult to assist when I don't
> have full information of what you are doing. If that script performs
This is a piece of code in the script:

CREATE TABLE X.DUMMY (
ATTIVITA varchar(30) NOT NULL,
DATA datetime NOT NULL,
UTENTE varchar(30) NOT NULL,
TEMPO datetime NULL,
NOTA varchar(50) NULL,
DBVERS varchar(15) NULL,
EURO varchar(1) NOT NULL,
PRIMARY KEY (DATA, ATTIVITA, UTENTE)
)

go
insert into X.DUMMY (data, attivita, utente, nota, euro, dbvers)
values ('01/01/1999','POTWIN3','PROJECT','NON ELIMINARE MAI!',
'S', '038')

But the problem is not on the script (this script, with obviously
differences is used to create db in DB2 and Oracle), because I've tried to
launch it from SQL Query Analyzer and it works well (using as default the
administrator user 'sa').
The problem seems to be the authorizations for user X, used to create the
schema of the DB, created as administrator in SQL Server. When I look the
properties of the table, it seems that X has the rights to insert, delete
and update data in the table.

> By the way, what does SELECT @.@.version say on your server?

Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation Standard Edition on Windows
NT 5.0 (Build 2195: Service Pack 4)

HELP cannot start sqlserver.....

Help
We changed the Administrator Password, and now I cannot start Sqlserver on one of my systems.
And no password I try will apparently bring it back online
What can I do to reset password etc.What error are you getting? Is that the account used by the SQL Service? If
so, you'll have to update the password in the Services applet, for
MSSQLServer service.
--
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"sqlstopper" <sqlstopper@.discussions.microsoft.com> wrote in message
news:756D0E5C-4E50-4098-8D26-9DD5ABF1490F@.microsoft.com...
> Help
> We changed the Administrator Password, and now I cannot start Sqlserver on
one of my systems.
> And no password I try will apparently bring it back online
> What can I do to reset password etc.|||You could change the password through Enterprise manager under Security >>
Logins, right click the logins and go into Properties and change, for SQL
Logins.
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.|||Is this the sa password? Can you try logging on using a Windows NT account
taht has admin rights?
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.sql

HELP cannot start sqlserver.....

Help
We changed the Administrator Password, and now I cannot start Sqlserver on one of my systems.
And no password I try will apparently bring it back online
What can I do to reset password etc.
What error are you getting? Is that the account used by the SQL Service? If
so, you'll have to update the password in the Services applet, for
MSSQLServer service.
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"sqlstopper" <sqlstopper@.discussions.microsoft.com> wrote in message
news:756D0E5C-4E50-4098-8D26-9DD5ABF1490F@.microsoft.com...
> Help
> We changed the Administrator Password, and now I cannot start Sqlserver on
one of my systems.
> And no password I try will apparently bring it back online
> What can I do to reset password etc.
|||The error I get is: Did not start due to logon failure.
Just where do I change the password?
regards
"Narayana Vyas Kondreddi" wrote:

> What error are you getting? Is that the account used by the SQL Service? If
> so, you'll have to update the password in the Services applet, for
> MSSQLServer service.
> --
> Vyas, MVP (SQL Server)
> http://vyaskn.tripod.com/
>
> "sqlstopper" <sqlstopper@.discussions.microsoft.com> wrote in message
> news:756D0E5C-4E50-4098-8D26-9DD5ABF1490F@.microsoft.com...
> one of my systems.
>
>
|||OK that did the trick!!!!
thank you very much
I just love you 'MVP (SQL Server)'-guys
Wonderfull Job
thx
"Narayana Vyas Kondreddi" wrote:

> What error are you getting? Is that the account used by the SQL Service? If
> so, you'll have to update the password in the Services applet, for
> MSSQLServer service.
> --
> Vyas, MVP (SQL Server)
> http://vyaskn.tripod.com/
>
> "sqlstopper" <sqlstopper@.discussions.microsoft.com> wrote in message
> news:756D0E5C-4E50-4098-8D26-9DD5ABF1490F@.microsoft.com...
> one of my systems.
>
>
|||You could change the password through Enterprise manager under Security >>
Logins, right click the logins and go into Properties and change, for SQL
Logins.
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.
|||Is this the sa password? Can you try logging on using a Windows NT account
taht has admin rights?
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.

HELP cannot start sqlserver.....

Help
We changed the Administrator Password, and now I cannot start Sqlserver on o
ne of my systems.
And no password I try will apparently bring it back online
What can I do to reset password etc.What error are you getting? Is that the account used by the SQL Service? If
so, you'll have to update the password in the Services applet, for
MSSQLServer service.
--
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"sqlstopper" <sqlstopper@.discussions.microsoft.com> wrote in message
news:756D0E5C-4E50-4098-8D26-9DD5ABF1490F@.microsoft.com...
> Help
> We changed the Administrator Password, and now I cannot start Sqlserver on
one of my systems.
> And no password I try will apparently bring it back online
> What can I do to reset password etc.|||The error I get is: Did not start due to logon failure.
Just where do I change the password?
regards
"Narayana Vyas Kondreddi" wrote:

> What error are you getting? Is that the account used by the SQL Service? I
f
> so, you'll have to update the password in the Services applet, for
> MSSQLServer service.
> --
> Vyas, MVP (SQL Server)
> http://vyaskn.tripod.com/
>
> "sqlstopper" <sqlstopper@.discussions.microsoft.com> wrote in message
> news:756D0E5C-4E50-4098-8D26-9DD5ABF1490F@.microsoft.com...
> one of my systems.
>
>|||OK that did the trick!!!!
thank you very much
I just love you 'MVP (SQL Server)'-guys
Wonderfull Job
thx
"Narayana Vyas Kondreddi" wrote:

> What error are you getting? Is that the account used by the SQL Service? I
f
> so, you'll have to update the password in the Services applet, for
> MSSQLServer service.
> --
> Vyas, MVP (SQL Server)
> http://vyaskn.tripod.com/
>
> "sqlstopper" <sqlstopper@.discussions.microsoft.com> wrote in message
> news:756D0E5C-4E50-4098-8D26-9DD5ABF1490F@.microsoft.com...
> one of my systems.
>
>|||You could change the password through Enterprise manager under Security >>
Logins, right click the logins and go into Properties and change, for SQL
Logins.
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.|||Is this the sa password? Can you try logging on using a Windows NT account
taht has admin rights?
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.

Help (question from 70-228)

Dear All,
I need your help to answer this question below:
======
You are the administrator of a SQL Server 2000 computer.
The server contains a database named Sales. A company e-commerce
application uses the Sales database to store sales transactions. The
company Web site has grown in popularity, and database utilization has
increased.
You need to collect data about the utilization of server resources so
that you can provide capacity planning. You want to automate the
collection process so that information is gathered as quickly as possible.
What should you do?
a. Configure System Monitor to collect data and store it in a SQL Server
table
b. Create a SQL Server Agent job that executes the sp_statistics stored
procedure daily and places the results in a text file
c. Use SQL Profiler to trace server activity and store the results in
SQL Server tables
d. Configure SQL Server alerts to store information in the Windows
application event log
======
Thanks
Robert Lie
I believe the answer is a) The reason is that a) is the only choice that
covers the whole server, to elaborate
b) gives the information on indexes, so you cannot perform capacity planning
from it
c) gives SQL Server commands executed but does not give such things as CPU
usage, disk usage ect
d) gives alert based upon a certain event, so that event has to occur before
its fired.
a) With a however you can configure it to report usage of server stuff i.e.
CPU Usgage, memory usage ect over a specific time period, i.e. every 10
seconds.
Anyone with anything different ?
Peter
"Robert Lie" wrote:

> Dear All,
> I need your help to answer this question below:
> ======
> You are the administrator of a SQL Server 2000 computer.
> The server contains a database named Sales. A company e-commerce
> application uses the Sales database to store sales transactions. The
> company Web site has grown in popularity, and database utilization has
> increased.
> You need to collect data about the utilization of server resources so
> that you can provide capacity planning. You want to automate the
> collection process so that information is gathered as quickly as possible.
> What should you do?
> a. Configure System Monitor to collect data and store it in a SQL Server
> table
> b. Create a SQL Server Agent job that executes the sp_statistics stored
> procedure daily and places the results in a text file
> c. Use SQL Profiler to trace server activity and store the results in
> SQL Server tables
> d. Configure SQL Server alerts to store information in the Windows
> application event log
> ======
> Thanks
> Robert Lie
>

Help (question from 70-228)

Dear All,
I need your help to answer this question below:
====== You are the administrator of a SQL Server 2000 computer.
The server contains a database named Sales. A company e-commerce
application uses the Sales database to store sales transactions. The
company Web site has grown in popularity, and database utilization has
increased.
You need to collect data about the utilization of server resources so
that you can provide capacity planning. You want to automate the
collection process so that information is gathered as quickly as possible.
What should you do?
a. Configure System Monitor to collect data and store it in a SQL Server
table
b. Create a SQL Server Agent job that executes the sp_statistics stored
procedure daily and places the results in a text file
c. Use SQL Profiler to trace server activity and store the results in
SQL Server tables
d. Configure SQL Server alerts to store information in the Windows
application event log
======
Thanks
Robert LieI believe the answer is a) The reason is that a) is the only choice that
covers the whole server, to elaborate
b) gives the information on indexes, so you cannot perform capacity planning
from it
c) gives SQL Server commands executed but does not give such things as CPU
usage, disk usage ect
d) gives alert based upon a certain event, so that event has to occur before
its fired.
a) With a however you can configure it to report usage of server stuff i.e.
CPU Usgage, memory usage ect over a specific time period, i.e. every 10
seconds.
Anyone with anything different ?
Peter
"Robert Lie" wrote:
> Dear All,
> I need your help to answer this question below:
> ======> You are the administrator of a SQL Server 2000 computer.
> The server contains a database named Sales. A company e-commerce
> application uses the Sales database to store sales transactions. The
> company Web site has grown in popularity, and database utilization has
> increased.
> You need to collect data about the utilization of server resources so
> that you can provide capacity planning. You want to automate the
> collection process so that information is gathered as quickly as possible.
> What should you do?
> a. Configure System Monitor to collect data and store it in a SQL Server
> table
> b. Create a SQL Server Agent job that executes the sp_statistics stored
> procedure daily and places the results in a text file
> c. Use SQL Profiler to trace server activity and store the results in
> SQL Server tables
> d. Configure SQL Server alerts to store information in the Windows
> application event log
> ======> Thanks
> Robert Lie
>

Help (question from 70-228)

Dear All,
I need your help to answer this question below:
======
You are the administrator of a SQL Server 2000 computer.
The server contains a database named Sales. A company e-commerce
application uses the Sales database to store sales transactions. The
company Web site has grown in popularity, and database utilization has
increased.
You need to collect data about the utilization of server resources so
that you can provide capacity planning. You want to automate the
collection process so that information is gathered as quickly as possible.
What should you do?
a. Configure System Monitor to collect data and store it in a SQL Server
table
b. Create a SQL Server Agent job that executes the sp_statistics stored
procedure daily and places the results in a text file
c. Use SQL Profiler to trace server activity and store the results in
SQL Server tables
d. Configure SQL Server alerts to store information in the Windows
application event log
======
Thanks
Robert LieI believe the answer is a) The reason is that a) is the only choice that
covers the whole server, to elaborate
b) gives the information on indexes, so you cannot perform capacity planning
from it
c) gives SQL Server commands executed but does not give such things as CPU
usage, disk usage ect
d) gives alert based upon a certain event, so that event has to occur before
its fired.
a) With a however you can configure it to report usage of server stuff i.e.
CPU Usgage, memory usage ect over a specific time period, i.e. every 10
seconds.
Anyone with anything different ?
Peter
"Robert Lie" wrote:

> Dear All,
> I need your help to answer this question below:
> ======
> You are the administrator of a SQL Server 2000 computer.
> The server contains a database named Sales. A company e-commerce
> application uses the Sales database to store sales transactions. The
> company Web site has grown in popularity, and database utilization has
> increased.
> You need to collect data about the utilization of server resources so
> that you can provide capacity planning. You want to automate the
> collection process so that information is gathered as quickly as possible.
> What should you do?
> a. Configure System Monitor to collect data and store it in a SQL Server
> table
> b. Create a SQL Server Agent job that executes the sp_statistics stored
> procedure daily and places the results in a text file
> c. Use SQL Profiler to trace server activity and store the results in
> SQL Server tables
> d. Configure SQL Server alerts to store information in the Windows
> application event log
> ======
> Thanks
> Robert Lie
>

Help

You are the administrator of a SQL Server 2000 computer. Each
evening after business hours, you perform database maintenance tasks. You
create a data transformation services package to populate the data
warehouse. MAPI is not installed on the server. You want to schedule the DTS
package to run each night. You want to attend to other duties while the DTS
package is executing. You also want to be notified if the DTS package fails.
What should you do?
A. Select the 'DTS packages log package execution to SQL
Server' check box. Create a SQL Server event alert to notify you if the
package fails.
B. Select the 'DTS packages write completion status to
event log' check box. Create a SQL Server event alert to notify you if the
package fails.
C. Configure the DTS package to include an on failure
precedence constraint and a send mail task. Configure the send mail task to
notify you if the package fails.
D. Configure the DTS package to include an on failure
precedence constraint and a send mail task. Configure the execute process
task to execute a not send command that will notify you if the package
fails.
Hi,
I will go with B option. In this option you could alert an email incase of
failure.
Thanks
Hari
MCDBA
"Noor" <noor@.ngsol.com> wrote in message
news:O8JNpQ5eEHA.2896@.TK2MSFTNGP11.phx.gbl...
> You are the administrator of a SQL Server 2000 computer. Each
> evening after business hours, you perform database maintenance tasks. You
> create a data transformation services package to populate the data
> warehouse. MAPI is not installed on the server. You want to schedule the
DTS
> package to run each night. You want to attend to other duties while the
DTS
> package is executing. You also want to be notified if the DTS package
fails.
> What should you do?
>
> A. Select the 'DTS packages log package execution to SQL
> Server' check box. Create a SQL Server event alert to notify you if the
> package fails.
> B. Select the 'DTS packages write completion status to
> event log' check box. Create a SQL Server event alert to notify you if the
> package fails.
> C. Configure the DTS package to include an on failure
> precedence constraint and a send mail task. Configure the send mail task
to
> notify you if the package fails.
> D. Configure the DTS package to include an on failure
> precedence constraint and a send mail task. Configure the execute process
> task to execute a not send command that will notify you if the package
> fails.
>
>
|||What about Option D , Net Send Command ?
Thanks
NOOR
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:#xvVx5WfEHA.708@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> Hi,
> I will go with B option. In this option you could alert an email incase of
> failure.
> Thanks
> Hari
> MCDBA
>
> "Noor" <noor@.ngsol.com> wrote in message
> news:O8JNpQ5eEHA.2896@.TK2MSFTNGP11.phx.gbl...
You[vbcol=seagreen]
> DTS
> DTS
> fails.
SQL[vbcol=seagreen]
the[vbcol=seagreen]
> to
process
>