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 Finding and Updating Dupes in 2 Tables

Being fairly new to SQL and SQL scripting, I am at a loss on how to proceed on my issue.

I have a MSDE database with 2 tables that need to modified. I am changing to a standard 12 digit code in my PATIENTS table for the field sChartCode nvarchar). That code will be in the form of 110012345678. 1100 will preceed the actual 8 digit chartcode

In the PATIENTS table, the same person may be duplicated many times using vaiations such as 123456, 12345678, 012345678, 12345678 SMITH, 012345678 SMITH. For each of these records, they are linked to the RECORDS db using the field lPatientId (int).

I have already manually updated about 20K records in the RECORDS db which
takes way to many hours of time. New records will be imported at about 10K a week or so and will be over 100K soon. By the way, the SQL server is on the way.

What I am looking for is an easier way to find the records that have not been
converted in the PATIENTS db and see if they match one that has already been converted. If it has, it would need to update all records in the RECORDS db with the correct updated lPatientId and then delete the duplicate record(s) from the PATENTS db. If not, it would only need to add '1100...' to the lPatientId field.

Any help or guidance that anybody can give will be most appreciated.

DaleSure, read the sticky at the top and supply the info we will need to help you...|||Here is my file structure for both of the databases:

Patients DB

lPatientId] [int]
sLastName] [nvarchar] (64)
sFirstName] [nvarchar] (64)
sMidName] [nvarchar] (64)
sChartCode] [nvarchar] (64)
lSex] [int]
dtBirthDate] [float]
sEthnicity] [nvarchar]
fWeight] [float]
sZipCode] [nvarchar]
sAddress1] [nvarchar]
sAddress2] [nvarchar]
sHomePhone] [nvarchar]
sWorkPhone] [nvarchar]
sCellPhone] [nvarchar]
sEMail] [nvarchar] (64)
sFax] [nvarchar] (64)
sComments] [nvarchar]

Records DB

lRecNo] [int]
lPatientId] [int]
lDoctorId] [int]
lDiagId] [int]
sEye] [nvarchar] (4)
sPhotoCode] [nvarchar] (64)
sProced] [nvarchar] (10)
dtProcDate] [datetime]
sImageType] [nvarchar]
iDisk] [smallint]
sSeriesInstanceUID] [nvarchar] (128)
sStudyInstanceUID] [nvarchar] (128)
sAccessionNumber] [nvarchar] (64)
lRefPhysicianId] [int]
lDbUniqueID] [int]
lAcquisitionSystem] [int]
lNumOfImages] [int]
lInUse] [int]
lPrevRecNo] [int]
sDictation] [nvarchar] (255)
lReviewed] [int]
lDigitalReport] [int]
lDicomSend] [int]
dtLastUpdate] [datetime]
lArchiveStatus] [int]
lStudyId] [int]
sSecPatientId] [nvarchar] (64)
sSecPatientNameCode] [nvarchar] (64)
lClinicId] [int]

Since I am not sure of what I really need to do to get this to work, I haven't done much. What I do have is:

update Patients
set sChartCode = '11' + REPLICATE('0', 10-len(sChartCode)) + sChartCode
where len(sChartCode) < 12

This works great until it gets to a duplicate records and I can't get past that until I resolve the duplicate.sql

Help - updating stored procs !

Hi all.

First post on here, so please excuse any ignorant parts of my question :)

We've recently had to migrate an application from one server to another, and had lots of problems getting it to work (NT4/SS7 -> 2K3/SS2K5). After much digging around I found the info on SQL server 2k5 changing the SUSER_NAME to SUSER_SNAME (I think from reading the change was actually from SQL Server 2000 - that correct ?).

Anyway I updated some of our stored procedures to correct that issue, and things *seem* to be working again (<-- famous last words, I know :eek: ), but I've been trying to find details of any other changes, particularly command changes, that I may need to make to our stored procedures.

To be honest I've found the info on Microsoft's main site and technet site next to useless - all seems to discuss very generic high-level procedures for database migration etc.. I didn't even find the SUSER change on there.

Could anyone point me to some useful information on other changes to look out for (within the SP's) ??

Thanks in advance for your help, and for taking the time to read this :beer:Hi all.

First post on here, so please excuse any ignorant parts of my question :)

We've recently had to migrate an application from one server to another, and had lots of problems getting it to work (NT4/SS7 -> 2K3/SS2K5). After much digging around I found the info on SQL server 2k5 changing the SUSER_NAME to SUSER_SNAME (I think from reading the change was actually from SQL Server 2000 - that correct ?).

Anyway I updated some of our stored procedures to correct that issue, and things *seem* to be working again (<-- famous last words, I know :eek: ), but I've been trying to find details of any other changes, particularly command changes, that I may need to make to our stored procedures.

To be honest I've found the info on Microsoft's main site and technet site next to useless - all seems to discuss very generic high-level procedures for database migration etc.. I didn't even find the SUSER change on there.

Could anyone point me to some useful information on other changes to look out for (within the SP's) ??

Thanks in advance for your help, and for taking the time to read this :beer:
I wasn't aware that SUSER_NAME is no longer supported. That surprises me.
I have upgraded several databases to 2005, and done a fair amount of sql programming in 2005, and I have not found anycode that is not backward compatible.|||fwiw, SUSER_NAME() and SUSER_SNAME() both work fine on my 2005 server (sp1).

Help - Updating A Field In Query Analyzer

Hi All,

I'm trying to create a script that updates a field in a table, based on data in another table. It should be simple, but I'm doing something wrong. Here's the code:

USE DBMyDatabase

UPDATE TblToBeUpdated
SET IDField=TblOther.IDNew
WHERE IDField=TblOther.IDOld

SELECT Pk, IDField
FROM TblToBeUpdated

What am I doing wrong? The error code I get is:

Server: Msg 107, Level 16, State 3, Line 1
The column prefix 'TblOther' does not match with a table name or alias name used in the query.
Server: Msg 107, Level 16, State 1, Line 1
The column prefix 'TblOther' does not match with a table name or alias name used in the query.

Thanks.

HenryUPDATE TblToBeUpdated
SET IDField=TblOther.IDNew
FROM TblToBeUpdated
inner join TblOther on TblToBeUpdated.IDField=TblOther.IDOld|||I very rarely use this syntax, but as I remember it:UPDATE tblToBeUpdated
SET IDField = b.IDNew
FROM tblToBeUpdated
JOIN tblOther AS b
ON (b.IDOld = tblToBeUpdated.IDField) should do the trick!

-PatP|||Thanks. Works beautifully.

Henry