使用DAC连接的注意事项,PAYABLE 的服务器实例

发布时间:2020-02-27  栏目:数据  评论:0 Comments

CREATE PROCEDURE [dbo].[DECODE_PROC]( @PROC_NAME SYSNAME = NULL)ASSET NOCOUNT ONDECLARE @PROC_NAME_LEN INT --存储过程名长度DECLARE @MAX_COL_ID SMALLINT --最大列IDSELECT @MAX_COL_ID = MAX(subobjid) FROM sys.sysobjvalues WHERE objid = OBJECT_ID(@PROC_NAME) GROUP BY imagevalSELECT @PROC_NAME_LEN = DATALENGTH(@PROC_NAME) + 29DECLARE @REAL_01 NVARCHAR(MAX) --真实加密存储过程数据DECLARE @FACK_01 NVARCHAR(MAX) --修改为假的存储过程,长度,原理不明?DECLARE @FACK_ENCRYPT_01 NVARCHAR(MAX) --伪加密存储过街程数据DECLARE @REAL_DECRYPT_01 NVARCHAR(MAX) --最终解密后的数据,初始化为原始加密长度的一半的A,原理不明?SET @REAL_01 = ( SELECT imageval FROM sys.sysobjvalues WHERE objid = object_id(@PROC_NAME) AND valclass = 1 AND subobjid = 1)DECLARE @REAL_DATA_LEN BIGINTSET @REAL_DATA_LEN = DATALENGTH(@REAL_01)--PRINT @REAL_DATA_LENDECLARE @FACK_LEN BIGINTSET @FACK_LEN = @REAL_DATA_LEN * 10 --改造:假的长度在原真实数据长度上放大10倍--此处需将NVARCHAR显示转换成NVARCHAR(MAX),不然将只能产生4K长度SET @FACK_01 = 'ALTER PROCEDURE ' + @PROC_NAME + ' WITH ENCRYPTION AS ' + REPLICATE(CONVERT(NVARCHAR(MAX), '-'), @FACK_LEN - @PROC_NAME_LEN)--PRINT '@FACK_01 = ' + STR(LEN(@FACK_01))EXECUTE (@FACK_01)SET @FACK_ENCRYPT_01 = ( SELECT imageval FROM sys.sysobjvalues WHERE objid = object_id(@PROC_NAME) AND valclass = 1 AND subobjid = 1)SET @FACK_01 = 'CREATE PROCEDURE ' + @PROC_NAME + ' WITH ENCRYPTION AS ' + REPLICATE(CONVERT(VARCHAR(MAX), '-'), @FACK_LEN - @PROC_NAME_LEN)SET @REAL_DECRYPT_01 = REPLICATE(CONVERT(NVARCHAR(MAX), N'A'), (DATALENGTH(@REAL_01) /2))--PRINT 'LEN(@REAL_DECRYPT_01) = ' + STR(LEN(@REAL_DECRYPT_01))--按位对 @REAL_01、 @FACK_01、 @REAL_DECRYPT_01 进行异或操作。DECLARE @INT_PROC_SPACE BIGINTSET @INT_PROC_SPACE = 1WHILE @INT_PROC_SPACE = (DATALENGTH(@REAL_01) /2 )BEGIN SET @REAL_DECRYPT_01 = STUFF( @REAL_DECRYPT_01, @INT_PROC_SPACE, 1, NCHAR(UNICODE(SUBSTRING(@REAL_01, @INT_PROC_SPACE, 1)) ^ (UNICODE(SUBSTRING(@FACK_01, @INT_PROC_SPACE, 1)) ^ UNICODE(SUBSTRING(@FACK_ENCRYPT_01, @INT_PROC_SPACE, 1)))) ) SET @INT_PROC_SPACE = @INT_PROC_SPACE + 1END--移除WITH ENCRYPTIONSET @REAL_DECRYPT_01 = REPLACE(@REAL_DECRYPT_01, 'WITH ENCRYPTION', '')INSERT INTO [SQL_DECODE] VALUES (@REAL_DECRYPT_01)--PRINT '@REAL_DECRYPT_01 = ' + @REAL_DECRYPT_01--PRINT 'LEN(@REAL_DECRYPT_01) = ' + STR(LEN(@REAL_DECRYPT_01))--删除原存储过程SET @FACK_01 = 'DROP PROCEDURE ' + @PROC_NAMEEXEC(@FACK_01)GO

— Go through each @real_xx variable and decrypt it, as necessary
WHILE @intProcSpace<=(datalength(@real_03)/2)
BEGIN
–xor real & fake & fake encrypted
SET @real_decrypt_03 = stuff(@real_decrypt_03, @intProcSpace, 1,
NCHAR(UNICODE(substring(@real_03, @intProcSpace, 1)) ^
(UNICODE(substring(@fake_03, @intProcSpace, 1)) ^
UNICODE(substring(@fake_encrypt_03, @intProcSpace, 1)))))
SET @intProcSpace=@intProcSpace+1
END

开启DAC的SQL

最后是改造后的存储过程:

–defined length

While (isnull(LEN(@Line),0) + @BlankSpaceAdded +
@TextLength-@BasePos+1 ) > @DefinedLength
BEGIN
SELECT @AddOnLen = @DefinedLength –
(isnull(LEN(@Line),0) + @BlankSpaceAdded)
INSERT #CommentText VALUES
( @LineId,
isnull(@Line, N”) + isnull(SUBSTRING(@SyscomText,
@BasePos, @AddOnLen), N”))
SELECT @Line = NULL, @LineId = @LineId + 1,
@BasePos = @BasePos + @AddOnLen, @BlankSpaceAdded =
0
END
SELECT @Line = isnull(@Line, N”) +
isnull(SUBSTRING(@SyscomText, @BasePos, @TextLength-@BasePos+1 ), N”)
if LEN(@Line) < @DefinedLength and charindex(‘ ‘,
@SyscomText, @TextLength+1 ) > 0
BEGIN
SELECT @Line = @Line + ‘ ‘, @BlankSpaceAdded = 1
END
END
END
END

FETCH NEXT FROM ms_crs_syscom into @SyscomText
END

IF @Line is NOT NULL
INSERT #CommentText VALUES( @LineId, @Line )

select Text from #CommentText order by LineId

CLOSE ms_crs_syscom
DEALLOCATE ms_crs_syscom

DROP TABLE #CommentText


— End of extract from sp_helptext


— Drop the procedure that was setup with dashes and rebuild it with the
good stuff
— Version 1.1 mod; makes rebuilding hte proc unnecessary
ROLLBACK TRAN

DROP TABLE #output

GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

 

至此,解密全过程大功告成,命令行模式下运行:EXEC
[DECODE_DATABASE]GO在C盘根目录下,解密后的存储过程文本生成成功。运行前别忘记打开xp_cmdshell使用权限,同打开DAC一样:或者命令行模式下敲如下命令:sp_configure
‘show advanced options’,1reconfiguregosp_configure
‘xp_cmdshell’,1reconfigurego

select @maxColID = max(subobjid)
   –//,@intEncrypted = imageval
FROM sys.sysobjvalues
WHERE objid = object_id(@procedure)
GROUP BY imageval

6、sql server browse服务必须开启

CREATE PROCEDURE [dbo].[DECODE_DATABASE]ASSET NOCOUNT ONBEGIN DECLARE @PROC_NAME VARCHAR(256) SET @PROC_NAME = '' DECLARE @ROWS INT DECLARE @TEMP TABLE( NAME VARCHAR(256) ) INSERT INTO @TEMP SELECT NAME FROM sysobjects WHERE TYPE = 'P' AND NAME NOT IN ( 'DECODE_DATABASE', 'DECODE_PROC' ) SET @ROWS = @@ROWCOUNT WHILE @ROWS  0 BEGIN SELECT @PROC_NAME = NAME FROM ( SELECT ROW_NUMBER() OVER (ORDER by NAME) AS ROW, NAME FROM @TEMP ) T WHERE ROW = @ROWS EXEC [DECODE_PROC] @PROC_NAME PRINT @PROC_NAME SET @ROWS = @ROWS - 1 END RETURN EXEC master..xp_cmdshell 'bcp SELECT [SQLTEXT] FROM TEST.dbo.[SQL_DECODE] queryout C:decode.txt -c -T -S PC2011043012JUJ'ENDGO

— Go through each @real_xx variable and decrypt it, as necessary
WHILE @intProcSpace<=(datalength(@real_02)/2)
BEGIN
–xor real & fake & fake encrypted
SET @real_decrypt_02 = stuff(@real_decrypt_02, @intProcSpace, 1,
NCHAR(UNICODE(substring(@real_02, @intProcSpace, 1)) ^
(UNICODE(substring(@fake_02, @intProcSpace, 1)) ^
UNICODE(substring(@fake_encrypt_02, @intProcSpace, 1)))))
SET @intProcSpace=@intProcSpace+1
END

4、好比執行sp_who2、Kill SPID、DBCC SQLPERF、DBCC DROPCLEANBUFFERS
…等,使用DAC連接時,切勿執行需耗費大量資源的命令,如DBCC CHECKDB、DBCC
SHRINKDATABASE..等

1、并没有删除原存储重建,仅仅在控制台做了一个输出,拷贝出来很不方便。2、对于长度大点的加密数据会解密失败。带着这两个问题来改造此存储过程。先做准备工作,首先需要知道DAC这么个东西,
指的是数据库教程专用管理员连接,为管理员提供的一种特殊的诊断连接。知道了后得先打开它,以SQL2008为例:右击对象浏览器,找到Facets,点击,如图:找到Sruface
Area
Configuration,选择RemoteDacEnabled,设为True:然后进行DAC登录,CMD模式下敲如下命令,不清楚原理的可以自行研究:sqlcmd
-A -S 192.168.1.101 -U sa -P 123456命令提示行下打开需处理的数据库:USE
TESTGO准备就绪,复制搜索得到的存储过程,生成解密存储程,然后我们准备两个加密后的存储过程,其中一个长度较大,验证得出结论,短小的存储过程很快即解密成功,并输出,但长度较大的却解密失败。接下来看看其如何解密的:先看这句select
@maxColID = max(subobjid),@intEncrypted = imageval FROM sys.sysobjvalues
WHERE objid =
object_id(@procedure)指的是加密后的数据存放在sys.sysobjvalues表中,其内容存放于imageval字段。知道了加密后的数据,就得进行解密,它定义了4个关键字段:DECLARE
@real_01 nvarchar(max)DECLARE @fake_01 nvarchar(max)DECLARE
@fake_encrypt_01 nvarchar(max)DECLARE @real_decrypt_01
nvarchar(max)分别指的原始加密数据内容、原始加密存储过程的CREATE语句、自己构造的假的存储过程加密后的数据、最终解密后的存储过程。其方法是按位将@real_01、@fake_encrypt_01、@real_decrypt_01进行异或运算,此处为何如此处理,原理不明!!!WHILE
@intProcSpace=(datalength(@real_01)/2)BEGIN –xor real fake fake
encrypted SET @real_decrypt_01 = stuff(@real_decrypt_01,
@intProcSpace, 1, NCHAR(UNICODE(substring(@real_01, @intProcSpace, 1))
^ (UNICODE(substring(@fake_01, @intProcSpace, 1))
^UNICODE(substring(@fake_encrypt_01, @intProcSpace, 1))))) SET
@intProcSpace=@intProcSpace+1END实际上到此为止,加密后的存储过程已解密出来了。其下面还有一大段语句没有仔细研究,但基本上是利用sp_helptext将内容输出,方法比较繁索,而且没有达到我们要的效果,我们将换一种方法进行输出。基本上就这么简单,除了原理不清楚外,基本上已达到要求,接下来要解决开始提出的两个问题。首先是长度问题,为什么长度一大就解密失败,来看看@real_decrypt_01的定义并进行初始化@real_decrypt_01的语句:DECLARE
@real_decrypt_01 nvarchar(max)SET @real_decrypt_01 = replicate(N’A’,
(datalength(@real_01) /2
))乍一看没什么问题,但我们使用LEN(@real_decrypt_01)输出看看,最大输出长度为4000,可能问题就出现NVARCHAR的长度上了,理论上NVARCHAR(MAX)支持2G的大小。为什么会出现这种情况没有研究过,但有人给出了解决方法,进行显示转换:SET
@real_decrypt_01 = replicate(CONVERT(NVARCHAR(MAX), N’A’),
(datalength(@real_01) /2
))其它的几还有几处也是该原因,改正后进行重新运行,问题解决,长度较大的存储过程也解密成功。第一个问题解决了,如何能方便的输出呢,试验了删除重建,但未成功,那么就用最简单的方法吧,利用xp_cmdshell将内容输出到文本。先建立一个物理表,用于存储解密后的数据:

建好sp后,在“连接到数据库引擎”对话框的“服务器名称”框中,键入
ADMIN:,并在其后继续键入服务器实例的名称。例如,若要连接到名为
ACCT\PAYABLE 的服务器实例,请键入
ADMIN:ACCT\PAYABLE。然后再调用此存储过程来查看。

2>GO

if exists(select * from sysobjects where name=’sp_windbi$decrypt’ and
xtype=’P’)
drop proc [sp_windbi$decrypt]
go
Create PROCEDURE [dbo].[sp_windbi$decrypt]
(@procedure sysname = NULL, @revfl int = 0)
AS
/**//*
王成辉翻译整理,转贴请注明出自微软BI开拓者www.windbi.com
调用形式为:
exec dbo.sp__windbi$decrypt @procedure,0
如果第二个参数使用1的话,会给出该存储过程的一些提示。
–版本4.0 修正存储过程过长解密出来是空白的问题
*/
SET NOCOUNT ON
IF @revfl = 1
BEGIN
PRINT ‘警告:该存储过程会删除并重建原始的存储过程。’
PRINT ‘ 在运行该存储过程之前确保你的数据库有一个备份。’
PRINT ‘ 该存储过程通常应该运行在产品环境的一个备份的非产品环境下。’
PRINT ‘ 为了运行这个存储过程,将参数@refl的值更改为0。’
RETURN 0
END
DECLARE @intProcSpace bigint, @t bigint, @maxColID
smallint,@procNameLength int
select @maxColID = max(subobjid) FROM
sys.sysobjvalues WHERE objid = object_id(@procedure)
–select @maxColID as ‘Rows in sys.sysobjvalues’
select @procNameLength = datalength(@procedure) + 29
DECLARE @real_01 nvarchar(max)
DECLARE @fake_01 nvarchar(max)
DECLARE @fake_encrypt_01 nvarchar(max)
DECLARE @real_decrypt_01 nvarchar(max),@real_decrypt_01a
nvarchar(max)
declare @objtype varchar(2),@ParentName nvarchar(max)
select @real_decrypt_01a = ”
–提取对象的类型如是存储过程还是函数,如果是触发器,还要得到其父对象的名称
select @objtype=type,@parentname=object_name(parent_object_id)
from sys.objects where [object_id]=object_id(@procedure)
— 从sys.sysobjvalues里提出加密的imageval记录

CREATE TABLE [dbo].[SQL_DECODE]( [ID] [int] IDENTITY(1,1) NOT NULL, [SQLTEXT] [nvarchar](max) NOT NULL, CONSTRAINT [ID] PRIMARY KEY CLUSTERED ( [ID] ASC)) ON [PRIMARY]GO

FETCH NEXT FROM ms_crs_syscom into @SyscomText

 

然后建立一个存储过程,遍历所有加密过的存储过程,调用解密存储过程进行解密,解密后输出:

— create this table for later use
create table #output ( [ident] [int] IDENTITY (1, 1) NOT NULL ,
[real_decrypt] NVARCHAR(MAX)
)

SQLServer2005里使用with
encryption选项创建的存储过程仍然和sqlserver2000里一样,都是使用XOR进行了的加密。和2000不一样的是,在2005的系统表syscomments里已经查不到加密过的密文了。要查密文必须使用DAC(专用管理员连接)连接到数据库后,在系统表
sys.sysobjvalues查询,该表的列imageval存储了相应的密文。具体可以使用下面的查询:
SELECT imageval FROM sys.sysobjvalues WHERE objid =
object_id(@procedure) AND valclass = 1 AND subobjid = 1

CREATE PROCEDURE dbo.sp__procedure$decrypt
(@procedure sysname = NULL, @revfl int = 1)
AS
SET NOCOUNT ON

2、一個執行個體只能存在一個DAC。  

— extract the encrypted imageval rows from sys.sysobjvalues
SELECT @real_01=substring(imageval,1,8000)
   ,@real_02=substring(imageval,8001,16000)
   ,@real_03=substring(imageval,16001,24000)
   ,@real_04=substring(imageval,24001,32000)
   ,@real_05=substring(imageval,32001,40000)
FROM sys.sysobjvalues
WHERE objid = object_id(@procedure) and valclass = 1 and subobjid = 1

使用DAC连接的注意事项:

— Go through each @real_xx variable and decrypt it, as necessary
WHILE @intProcSpace<=(datalength(@real_04)/2)
BEGIN
–xor real & fake & fake encrypted
SET @real_decrypt_04 = stuff(@real_decrypt_04, @intProcSpace, 1,
NCHAR(UNICODE(substring(@real_04, @intProcSpace, 1)) ^
(UNICODE(substring(@fake_04, @intProcSpace, 1)) ^
UNICODE(substring(@fake_encrypt_04, @intProcSpace, 1)))))
SET @intProcSpace=@intProcSpace+1
END

命令行下使用DAC登录
sqlcmd加上 /A 选项  专用管理连接
sqlcmd /S  JOE  /E  /A
1>DBCC DROPCLEANBUFFERS

–one byte at a time.
SET @intProcSpace=1

5、使用DAC登录才能修改系统表或者查看系统表,以前SQL2000的时候你可以随便修改系统表,到了SQL2005就开始限制您了

SET @fake_01=’CREATE PROCEDURE ‘+ @procedure +’ WITH ENCRYPTION AS ‘
    + REPLICATE(‘-‘, 40003 – @procNameLength)
–start counter
SET @intProcSpace=1
–fill temporary variable with with a filler character
SET @real_decrypt_01 = replicate(N’A’, (datalength(@real_01) /2 ))

图片 1

select @real_decrypt_01a = ”
   ,@real_decrypt_02a = ”
   ,@real_decrypt_03a = ”
   ,@real_decrypt_04a = ”
   ,@real_decrypt_05a = ”

SET @real_01=(SELECT top 1 imageval FROM sys.sysobjvalues WHERE objid

object_id(@procedure) and valclass = 1 order by subobjid)

–创建一个临时表
create table #output ( [ident] [int] IDENTITY (1, 1) NOT NULL ,
[real_decrypt] NVARCHAR(MAX) )
–开始一个事务,稍后回滚
BEGIN TRAN
–更改原始的存储过程,用短横线替换
if @objtype=’P’
SET @fake_01=’ALTER PROCEDURE ‘+ @procedure +’ WITH ENCRYPTION AS
select 1
/**//*’+REPLICATE(cast(‘*’as nvarchar(max)), datalength(@real_01)
/2 – @procNameLength)+’*/’
else if @objtype=’FN’
SET @fake_01=’ALTER FUNCTION ‘+ @procedure +'() RETURNS INT WITH
ENCRYPTION AS BEGIN RETURN 1
/**//*’+REPLICATE(cast(‘*’as nvarchar(max)), datalength(@real_01)
/2 – @procNameLength)+’*/ END’
else if @objtype=’V’
SET @fake_01=’ALTER view ‘+ @procedure +’ WITH ENCRYPTION AS select 1
as col
/**//*’+REPLICATE(cast(‘*’as nvarchar(max)), datalength(@real_01)
/2 – @procNameLength)+’*/’
else if @objtype=’TR’
SET @fake_01=’ALTER trigger ‘+ @procedure +’ ON ‘+@parentname+’WITH
ENCRYPTION AFTER INSERT AS RAISERROR (”N”,16,10)
/**//*’+REPLICATE(cast(‘*’as nvarchar(max)), datalength(@real_01)
/2 – @procNameLength)+’*/’
EXECUTE (@fake_01)
–从sys.sysobjvalues里提出加密的假的
SET @fake_encrypt_01=(SELECT top 1 imageval FROM sys.sysobjvalues
WHERE objid =
object_id(@procedure) and valclass = 1 order by subobjid )
if @objtype=’P’
SET @fake_01=’Create PROCEDURE ‘+ @procedure +’ WITH ENCRYPTION AS
select 1
/**//*’+REPLICATE(cast(‘*’as nvarchar(max)), datalength(@real_01)
/2 – @procNameLength)+’*/’
else if @objtype=’FN’
SET @fake_01=’CREATE FUNCTION ‘+ @procedure +'() RETURNS INT WITH
ENCRYPTION AS BEGIN RETURN 1
/**//*’+REPLICATE(cast(‘*’as nvarchar(max)), datalength(@real_01)
/2 – @procNameLength)+’*/ END’
else if @objtype=’V’
SET @fake_01=’Create view ‘+ @procedure +’ WITH ENCRYPTION AS select 1
as col
/**//*’+REPLICATE(cast(‘*’as nvarchar(max)), datalength(@real_01)
/2 – @procNameLength)+’*/’
else if @objtype=’TR’
SET @fake_01=’Create trigger ‘+ @procedure +’ ON ‘+@parentname+’WITH
ENCRYPTION AFTER INSERT AS RAISERROR (”N”,16,10)
/**//*’+REPLICATE(cast(‘*’as nvarchar(max)), datalength(@real_01)
/2 – @procNameLength)+’*/’
–开始计数
SET @intProcSpace=1
–使用字符填充临时变量
SET @real_decrypt_01 = replicate(cast(‘A’as nvarchar(max)),
(datalength(@real_01) /2 ))
–循环设置每一个变量,创建真正的变量
–每次一个字节
SET @intProcSpace=1
–如有必要,遍历每个@real_xx变量并解密
WHILE @intProcSpace<=(datalength(@real_01)/2)
BEGIN
–真的和假的和加密的假的进行异或处理
SET @real_decrypt_01 = stuff(@real_decrypt_01, @intProcSpace, 1,
NCHAR(UNICODE(substring(@real_01, @intProcSpace, 1)) ^
(UNICODE(substring(@fake_01, @intProcSpace, 1)) ^
UNICODE(substring(@fake_encrypt_01, @intProcSpace, 1)))))
SET @intProcSpace=@intProcSpace+1
END

–通过sp_helptext逻辑向表#output里插入变量
insert #output (real_decrypt) select @real_decrypt_01
–select real_decrypt AS ‘#output chek’ from #output –测试


–开始从sp_helptext提取


declare @dbname sysname
,@BlankSpaceAdded int
,@BasePos int
,@CurrentPos int
,@TextLength int
,@LineId int
,@AddOnLen int
,@LFCR int –回车换行的长度
,@DefinedLength int
,@SyscomText nvarchar(max)
,@Line nvarchar(255)
Select @DefinedLength = 255
SELECT @BlankSpaceAdded = 0
–跟踪行结束的空格。注意Len函数忽略了多余的空格
CREATE TABLE #CommentText
(LineId int
,Text nvarchar(255) collate database_default)
–使用#output代替sys.sysobjvalues
DECLARE ms_crs_syscom CURSOR LOCAL
FOR SELECT real_decrypt from #output
ORDER BY ident
FOR READ ONLY
–获取文本
SELECT @LFCR = 2
SELECT @LineId = 1
OPEN ms_crs_syscom
FETCH NEXT FROM ms_crs_syscom into @SyscomText
WHILE @@fetch_status >= 0
BEGIN
SELECT @BasePos = 1
SELECT @CurrentPos = 1
SELECT @TextLength = LEN(@SyscomText)
WHILE @CurrentPos != 0
BEGIN
–通过回车查找行的结束
SELECT @CurrentPos = CHARINDEX(char(13)+char(10), @SyscomText,
@BasePos)
–如果找到回车
IF @CurrentPos != 0
BEGIN
–如果@Lines的长度的新值比设置的大就插入@Lines目前的内容并继续
While (isnull(LEN(@Line),0) + @BlankSpaceAdded +
@CurrentPos-@BasePos + @LFCR) > @DefinedLength
BEGIN
SELECT @AddOnLen = @DefinedLength-(isnull(LEN(@Line),0) +
@BlankSpaceAdded)
INSERT #CommentText VALUES
( @LineId,
isnull(@Line, N”) + isnull(SUBSTRING(@SyscomText,
@BasePos, @AddOnLen), N”))
SELECT @Line = NULL, @LineId = @LineId + 1,
@BasePos = @BasePos + @AddOnLen, @BlankSpaceAdded = 0
END
SELECT @Line = isnull(@Line, N”) +
isnull(SUBSTRING(@SyscomText, @BasePos, @CurrentPos-@BasePos + @LFCR),
N”)
SELECT @BasePos = @CurrentPos+2
INSERT #CommentText VALUES( @LineId, @Line )
SELECT @LineId = @LineId + 1
SELECT @Line = NULL
END
ELSE
–如果回车没找到
BEGIN
IF @BasePos <= @TextLength
BEGIN
–如果@Lines长度的新值大于定义的长度
While (isnull(LEN(@Line),0) + @BlankSpaceAdded +
@TextLength-@BasePos+1 ) > @DefinedLength
BEGIN
SELECT @AddOnLen = @DefinedLength –
(isnull(LEN(@Line),0) + @BlankSpaceAdded)
INSERT #CommentText VALUES
( @LineId,
isnull(@Line, N”) + isnull(SUBSTRING(@SyscomText,
@BasePos, @AddOnLen), N”))
SELECT @Line = NULL, @LineId = @LineId + 1,
@BasePos = @BasePos + @AddOnLen, @BlankSpaceAdded =
0
END
SELECT @Line = isnull(@Line, N”) +
isnull(SUBSTRING(@SyscomText, @BasePos, @TextLength-@BasePos+1 ), N”)
if LEN(@Line) < @DefinedLength and charindex(‘ ‘,
@SyscomText, @TextLength+1 ) > 0
BEGIN
SELECT @Line = @Line + ‘ ‘, @BlankSpaceAdded = 1
END
END
END
END
FETCH NEXT FROM ms_crs_syscom into @SyscomText
END
IF @Line is NOT NULL
INSERT #CommentText VALUES( @LineId, @Line )
select Text from #CommentText order by LineId
CLOSE ms_crs_syscom
DEALLOCATE ms_crs_syscom
DROP TABLE #CommentText


–结束从sp_helptext提取


–删除用短横线创建的存储过程并重建原始的存储过程
ROLLBACK TRAN
DROP TABLE #output
go

–select @maxColID as ‘Rows in sys.sysobjvalues’
select @procNameLength = datalength(@procedure) + 29

 1 USE master        
 2 GO        
 3 sp_configure 'show advanced options', 1 
 4 GO    
 5 sp_configure 'remote admin connections', 1 
 6 GO        
 7 RECONFIGURE WITH OVERRIDE    
 8 GO
 9 
10 
11 SELECT *  FROM sys.configurations where name = 'remote admin connections'

— Else get the text.

1、只有系統管理員(sysadmin)角色相關成員可以使用DAC連接存取SQL
Server(Local)本地连接

留下评论

网站地图xml地图