Re: Stored procedure/trigger and scripts

From: Scott Elgram (SElgram_at_verifpoint.com)
Date: 02/27/04 

Date: Fri, 27 Feb 2004 11:41:15 -0800

When I set the following trigger

CREATE TRIGGER [tr_Test] ON [dbo].[Test]
FOR INSERT, UPDATE
AS
exec master..xp_CMDShell "C:\DB-2.vbs"

it locked me out of the table "Test". At first I was thinking this could
this be because the VBScript I wrote also runs a query against that table
but the script never ran (at least as far as I could tell). So I think it
must have been the trigger that locked up the table. Do you know why this
happened? 

-- 
-Scott Elgram
"Keith Kratochvil" <sqlguy.back2u@comcast.net> wrote in message
news:ubzQKUW$DHA.3220@TK2MSFTNGP10.phx.gbl...
SQL Server has permissions to execute xp_cmdshell.
xp_cmdshell will execute under different security contexts depending how
your application connects to the database.  If the app connects as sa/dbo or
other "privileged" user, then cmdshell will be run under the credentials
that the SQL Server service is running under.  If the app connects as a
"normal" database user or someone without administrative rights the extended
stored procedure xp_cmdshell will be executed within a proxy account with
limited rights.
That is why I suggested trying to schedule the execution via a job.  It
might be easier to get it running as you expect.  Depending on your current
configuration, it might also be more secure.
Please read up on xp_cmdshell within Books Online (within the SQL Server
program group).  You will find lots of valuable (and helpful) information.
-- 
Keith
"Scott Elgram" <SElgram@verifpoint.com> wrote in message
news:eMf$KtU$DHA.2636@TK2MSFTNGP09.phx.gbl...
>     DB-2.vbs is the VBScript I wrote that will create a local user on our
> report computer for every user in the client list table or if the local
user
> account already exists it will just change their password.  I have checked
> the permissions on the file and they are set to full for everyone.  Where
> can I check if SQL has permissions to run xp_cmdShell?
> -- 
> -Scott Elgram
>
> "Keith Kratochvil" <sqlguy.back2u@comcast.net> wrote in message
> news:e3lntCU$DHA.4080@TK2MSFTNGP09.phx.gbl...
> What does the vbs do?  If the vbs can run (and complete) totally
unattended
> it should work as long as the [SQL] has the appropriate permissions to
> execute xp_cmdshell.
>
>
> -- 
> Keith
>
>
> "Scott Elgram" <SElgram@verifpoint.com> wrote in message
> news:uY7EV7M$DHA.3536@tk2msftngp13.phx.gbl...
> > Keith,
> >     Actually, nothing is going on with the website at all.  In order for
a
> > client to change their password they have to call the "Client Relations"
> > department who change the password in the table with access.  I Have
> written
> > my VBScript file to do what I wanted and added the fallowing trigger;
> >
> > CREATE TRIGGER [tr_Test] ON [dbo].[Test]
> > FOR INSERT, UPDATE/*, DELETE */
> > AS
> > exec master..xp_CMDShell "C:\DB-2.vbs"
> >
> > I added this to a Test table to try it out and when I changed one record
> > nothing happened.  Well almost nothing......It locked me out of querying
> > that table until I stopped and restarted SQL all together.  I have never
> > used triggers before, did I get it wrong?
> > -Scott Elgram
> >
> > "Keith Kratochvil" <sqlguy.back2u@comcast.net> wrote in message
> > news:O0zB48I$DHA.684@tk2msftngp13.phx.gbl...
> > I don't know if you want to allow the web site to execute
> > master..xp_cmdshell....but perhaps you could have a process [scheduled
> job]
> > that looks at the password table and looks for passwords that have
changed
> > within the last x minutes (by comparing getdate() to a "LastUpdated"
> column
> > on the password table.  The job would then use xp_cmdshell to change the
> > user's NT password.  You indicated that this part was possible....you
just
> > need to know which user and what the new password is.  Hopefully my
> comments
> > allow you to figure out how to identify new/modified users and their
> > associated password.
> >
> > -- 
> > Keith
> >
> >
> > "Scott Elgram" <SElgram@verifpoint.com> wrote in message
> > news:ulT8FoI$DHA.3284@TK2MSFTNGP09.phx.gbl...
> > > Hello,
> > >     I am in need of a way to use a stored procedure/trigger to execute
a
> > > script.
> > >     Here's the situation.  About 3 weeks ago Microsoft released an
> update
> > to
> > > IE 6 that eliminated the ability to pass a username and password
through
> > the
> > > URL in a website.  My company used this feature to allow clients to
> access
> > > files on a local server from our hosted site and thanks to this update
I
> > > have been forced to find a new way of accomplishing this task.
Because
> > the
> > > initial site requires a login I decided to add each client manually
into
> > the
> > > local file server.  This resulted in the clients having to enter their
> > > username and password twice (once to enter the site and once to access
> the
> > > first file of a session).  Each clients user name and password is
> located
> > in
> > > a table on a SQL 7 server and the accounts for the local file server
> were
> > > entered manually by yours truly.  Fortunately the number of clients
> > > requiring this second user name and password was only enough to be a
> minor
> > > annoyance and not a huge undertaking.  However, because of all this
the
> > > Client Relations department now has to inform me every time a client
> > > requests their password be changed.
> > >     What I would like to do is somehow link an Add, Update and Delete
> > > trigger to execute a script which will update the user accounts and
> > > passwords on the local file server every time a change is made to the
> > > password field of the table.
> > >     The scripting part is possible.  However, I am unable to find any
> > > documentation on weather or not it is possible to execute a script or
> > > something of that nature from a stored procedure/trigger.
> > >     Is this possible?
> > >
> > > -- 
> > > -Scott Elgram
> > >
> > >
> >
> >
>
>

Relevant Pages

  • Re: sql server events on data create
    ... If I were to do this, I would set up the client to listen on a socket, write ... an extended stored procedure that can trigger this socket, ... triggers on the tables of interest to call this extended stored procedure, ... Or you could start a custom SQL Server trace event with the right filters? ...
    (microsoft.public.sqlserver.programming)
  • Re: Stored procedure/trigger and scripts
    ... I just ran some additional tests and it looks like my trigger is fine. ... SQL Server has permissions to execute xp_cmdshell. ... >> client to change their password they have to call the "Client Relations" ...
    (microsoft.public.sqlserver.programming)
  • Re: Stored procedure/trigger and scripts
    ... Can you run the vbs by calling it directly from SQL Server using xp_cmdshell (without a trigger)? ... If it does work this way I would probably create a job within SQL Server that would call the vbs at a scheduled interval. ... > SQL Server has permissions to execute xp_cmdshell. ...
    (microsoft.public.sqlserver.programming)
  • Re: call to xp_cmdshell from trigger problem
    ... The application user is a SQL Server account and not a Windows user account. ... try a test and see if the client gets the required info. ... >> database and causes a trigger to fire and starts a process to notify the ...
    (microsoft.public.sqlserver.server)
  • Re: Executing a dotnet console application from SQL Server 2005/2008
    ... Is the console application perhaps accessing data modified by the statement that fired the trigger? ... Keep in mind that the application is executing in a different session than the trigger and the modified data is still uncommitted. ... application that I want it to execute in a SQL trigger. ... I try to execute the code, the sql server management studio stays "running ...
    (microsoft.public.sqlserver.programming)