Re: Stored procedure/trigger and scripts

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

Date: Fri, 27 Feb 2004 13:00:56 -0800

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: 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
    ... 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
    ... 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)