Re: Stored procedure/trigger and scripts

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

Date: Fri, 27 Feb 2004 13:07:35 -0600

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: DataSet.GetChanges() in RowChanged(DataRowAction.Add)
    ... have you considered SQL Express and use ... > I realize now that I didn't describe well how the client application is ... > Framework installed on the client machine, but not any SQL Server). ... > 20 tables in different relations with eachother in the database, ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: SBS 2003 and Sql Server ~ Client Install
    ... I've found the client tools and have now successfully installed them. ... Access front end to a SQL database, Excel can be a front end, A word mail merge document could access a SQL database via ODBC. ... We are trying to install the client software. ... We understand that a Northwind training database is available for SQL Server. ...
    (microsoft.public.backoffice.smallbiz2000)
  • Re: SBS 2003 and Sql Server ~ Client Install
    ... I've found the client tools and have now ... A word mail merge document could access a SQL database via ... We want to install to gain an understanding. ... Once we've managed to install the SQL Server Client we can start to ...
    (microsoft.public.backoffice.smallbiz2000)
  • Re: TDS vs. TCP
    ... I start capturing the network traffic between SQL ... Server and the client. ... I don't see any TDS packets but the communication between SQL ... stand-a-lone server and the client, ...
    (microsoft.public.sqlserver.clustering)
  • Re: DMX Query response time
    ... the prediction is done in client-side. ... In SQL 2005, all predictions are performed in the server. ... >>>> server are remoted to the client and cached into client memory until ...
    (microsoft.public.sqlserver.datamining)