Re: Stored procedure/trigger and scripts

From: Keith Kratochvil (sqlguy.back2u_at_comcast.net)
Date: 02/27/04 

Date: Fri, 27 Feb 2004 15:38:06 -0600

Can you run the vbs outside of a trigger (via the command line)?
Does it do what you want?

Can you run the vbs by calling it directly from SQL Server using xp_cmdshell (without a trigger)?
Does it work?

If it does work this way I would probably create a job within SQL Server that would call the vbs at a scheduled interval. 

-- 
Keith
"Scott Elgram" <SElgram@verifpoint.com> wrote in message news:uBUZMTX$DHA.624@TK2MSFTNGP11.phx.gbl...
> I just ran some additional tests and it looks like my trigger is fine.  My
> initial assumption seems to have been correct.....For some reason because my
> VBScript is querying the same table that has the trigger it locks up.  I am
> still unsure why this is though, any thoughts?
> 
> -- 
> -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: Stored procedure/trigger and scripts
    ... The VBS runs from the command line and it Runs from when I use "EXEC ... Can you run the vbs outside of a trigger? ... If it does work this way I would probably create a job within SQL Server ... > SQL Server has permissions to execute xp_cmdshell. ...
    (microsoft.public.sqlserver.programming)
  • Re: Stored procedure/trigger and scripts
    ... must have been the trigger that locked up the table. ... 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
    ... 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: DELETE Trigger
    ... Name results in the execution of the .vbs. ... How would the trigger look? ... > procedure to execute the script. ... You could schedule this stored procedure ...
    (microsoft.public.sqlserver.programming)
  • 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)
Loading