I need to block some rows from being updated using mssql 2000.
this is because some users are changing data after the product has been
factured.
The VB program does not let any body to change the data but some advanced
users have done it directly to the database.
If some one can help me I will be very thankful
Regards, LinaWhy do those "advanced users" even have access to the database?
They should not.|||Given that you need to solve the problem as it exists, the simplest approach
would be to put a trigger on the table that checks to see if one of the
"non-alterable" records is being updated and roll back the transaction. You
might also want to log who is updating the row for future personnel related
actions.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
<Octavius@.gmail.com> wrote in message
news:1135822838.009333.252160@.g14g2000cwa.googlegroups.com...
> Why do those "advanced users" even have access to the database?
> They should not.
>|||Thanks a lot, Lina
"Roger Wolter[MSFT]" wrote:
> Given that you need to solve the problem as it exists, the simplest approa
ch
> would be to put a trigger on the table that checks to see if one of the
> "non-alterable" records is being updated and roll back the transaction. Y
ou
> might also want to log who is updating the row for future personnel relate
d
> actions.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> <Octavius@.gmail.com> wrote in message
> news:1135822838.009333.252160@.g14g2000cwa.googlegroups.com...
>
>|||You could try using the APP_NAME function. Run SQL Query Analyzer, and
execute sp_who2 while some users are connected. Look under the ProgramName
column to see what the name of your application is as it appears to the
server.
Then put some logic into your stored procedures, or into an INSTEAD OF
trigger on your table(s)
IF APP_NAME() <> 'YourApp'
--Sorry Charlie!
If your application doesn't report a ProgramName, but the "advanced users"
are using something like Access or a command line tool that does report a
ProgramName, you could block it with something like
IF APP_NAME() IN ('Microsoft Access', 'osql')
"Lina Manjarres" wrote:
> I need to block some rows from being updated using mssql 2000.
> this is because some users are changing data after the product has been
> factured.
> The VB program does not let any body to change the data but some advanced
> users have done it directly to the database.
> If some one can help me I will be very thankful
> Regards, Lina|||Thanks a lot Mark
I used this:
IF EXISTS(SELECT * FROM inserted WHERE facturado_Cliente = 1)
Begin
ROLLBACK TRAN
End
and i had a problem wit it. After the facturado_Cliente where changed to 0
again, it did not let me change the row any more. I mean, I couldn't make it
1 again, it allways rolled back.
So I will try what you suggest me, because it sound perfect. Is it posible
to do so, but with the HostName?
Thanks a lot, Lina
"Mark Williams" wrote:
> You could try using the APP_NAME function. Run SQL Query Analyzer, and
> execute sp_who2 while some users are connected. Look under the ProgramName
> column to see what the name of your application is as it appears to the
> server.
> Then put some logic into your stored procedures, or into an INSTEAD OF
> trigger on your table(s)
> IF APP_NAME() <> 'YourApp'
> --Sorry Charlie!
> If your application doesn't report a ProgramName, but the "advanced users"
> are using something like Access or a command line tool that does report a
> ProgramName, you could block it with something like
> IF APP_NAME() IN ('Microsoft Access', 'osql')
> "Lina Manjarres" wrote:
>|||The HOST_NAME() function will return the hostname of the system associated
with the connection, which you could use in a WHERE clause, but it is
probably not a good idea. You would have to block individual machine names,
and all someone would have to do in order to defeat your filter is
move to another machine , or
change their machine name
It would probably work out better to put in a filter condition based on
APP_NAME() . Does your application show a ProgramName value when you run
sp_who2 ? If it does, filter the updates based on the ProgramName.
"Lina Manjarres" wrote:
> Thanks a lot Mark
> I used this:
> IF EXISTS(SELECT * FROM inserted WHERE facturado_Cliente = 1)
> Begin
> ROLLBACK TRAN
> End
> and i had a problem wit it. After the facturado_Cliente where changed to 0
> again, it did not let me change the row any more. I mean, I couldn't make
it
> 1 again, it allways rolled back.
> So I will try what you suggest me, because it sound perfect. Is it posible
> to do so, but with the HostName?
> Thanks a lot, Lina
> "Mark Williams" wrote:
>|||Thanks a lot Mark.
One more question.
What happend when I have several SQL replicating. Is there any problem wit i
t?
"Mark Williams" wrote:
> The HOST_NAME() function will return the hostname of the system associated
> with the connection, which you could use in a WHERE clause, but it is
> probably not a good idea. You would have to block individual machine names
,
> and all someone would have to do in order to defeat your filter is
> move to another machine , or
> change their machine name
> It would probably work out better to put in a filter condition based on
> APP_NAME() . Does your application show a ProgramName value when you run
> sp_who2 ? If it does, filter the updates based on the ProgramName.
> "Lina Manjarres" wrote:
>|||If the table is involved in replication, you can place the application
checking logic into an INSTEAD OF trigger, and create the trigger with the
NOT FOR REPLICATION option. This will prevent the trigger from firing when a
replication operation modifies the table. See books online for more details.
If you posted to this forum through TechNet, and you found my answers
helpful, please mark them as answers. Thanks!
"Lina Manjarres" wrote:
> Thanks a lot Mark.
> One more question.
> What happend when I have several SQL replicating. Is there any problem wit
it?
> "Mark Williams" wrote:
>|||Dear Mark
I am using this:
AFTER UPDATE NOT FOR REPLICATION
Is it ok or do I have to use Instead of?
Thanks a lot, Lina
"Mark Williams" wrote:
> If the table is involved in replication, you can place the application
> checking logic into an INSTEAD OF trigger, and create the trigger with the
> NOT FOR REPLICATION option. This will prevent the trigger from firing when
a
> replication operation modifies the table. See books online for more detail
s.
> --
> If you posted to this forum through TechNet, and you found my answers
> helpful, please mark them as answers. Thanks!
>
> "Lina Manjarres" wrote:
>
