news:1135822838.009333.252160@g14g2000cwa.googlegroups.com...
> Why do those "advanced users" even have access to the database?
>
> They should not.
>

"Roger Wolter[MSFT]" wrote:

> 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
>
> <Octav***@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.
> >
>
>
>

"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

"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:
>
> > 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

"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:
>
> > 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

"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:
>
> > 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:
> > >
> > > > 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

"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:
>
> > 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:
> > >
> > > > 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

"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 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:
> >
> > > 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:
> > > >
> > > > > 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

"Lina Manjarres" 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 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:
> > >
> > > > 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:
> > > > >
> > > > > > 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

"Mark Williams" wrote:

> Since you want to prevent a change to the table based on a certain condition,
> you would want an instead of trigger. You can have both INSTEAD OF and AFTER
> triggers on the same table, but only one INSTEAD OF trigger can exist per
> table. You can have multiple AFTER triggers.
>
> INSTEAD OF triggers fire first, then AFTER triggers.
>
>
> --
> If you posted to this forum through TechNet, and you found my answers
> helpful, please mark them as answers.
>
>
> "Lina Manjarres" 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 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:
> > > >
> > > > > 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:
> > > > > >
> > > > > > > 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