Re: sp_password
From: Dan Guzman (danguzman_at_nospam-earthlink.net)
Date: 06/20/03
- Next message: James Miller: "Re : Database security"
- Previous message: Paul: "backup media / set - password protection"
- In reply to: Leon Parker: "sp_password"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]
Date: Fri, 20 Jun 2003 07:59:49 -0500
Your locally defined variables included in the xp_cmdshell string are
out of scope when OSQL is executed. Try:
DECLARE @OsqlCommand nvarchar(4000)
SET @OsqlCommand = N'osql -U ldp -P newldp -b ' +
N'-q "exec sp_password NULL, @new = ''' +
@NewPassword +
N''', @loginame = ''' + @loginname + N''''
EXEC master.dbo.xp_cmdshell @OsqlCommand
-- Hope this helps. Dan Guzman SQL Server MVP ----------------------- SQL FAQ links (courtesy Neil Pike): http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800 http://www.sqlserverfaq.com http://www.mssqlserver.com/faq ----------------------- "Leon Parker" <anonymous@devdex.com> wrote in message news:e3LeBaoNDHA.3976@tk2msftngp13.phx.gbl... > Here is a copy of my script where I'm passing in parameter names. > Whenever I execute the procedure I get the error message that the login > '@login' does not exist. Apparently my parameter is not being passed in > correctly. Can anyone help with the syntax here? > > IF @cnt >= 2 > Begin > > select @loginname = (select top 1 i.loginname from inserted i) > > set @msgtxt = 'SQL username account has been locked out: ' +@loginname > > exec master.dbo.xp_sendmail @recipients ='lparker.com', > @message = @msgtxt, > @subject = 'Login Access Violation' > > select @NewPassword = 'Lockout' + datename(ms,getdate()) > > EXEC master.dbo.xp_cmdshell 'osql -U ldp -P newldp -b -q "exec > sp_password NULL, @new = ''@NewPassword'', @loginame = ''@loginname'' " > ' > > > End > > > > > > > > Leon Parker > > *** Sent via Developersdex http://www.developersdex.com *** > Don't just participate in USENET...get rewarded for it!
- Next message: James Miller: "Re : Database security"
- Previous message: Paul: "backup media / set - password protection"
- In reply to: Leon Parker: "sp_password"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]
Relevant Pages
|
