oracle批量导出用户密码,MSSQL导出所有login账号和密码

1> 创建下面两个SP:

CREATE PROCEDURE [dbo].[sp_hexadecimal]

&＃64;binvalue varbinary(256),

&＃64;hexvalue varchar (514) OUTPUT

AS

DECLARE &＃64;charvalue varchar (514)

DECLARE &＃64;i int

DECLARE &＃64;length int

DECLARE &＃64;hexstring char(16)

SELECT &＃64;charvalue &＃61; &＃39;0x&＃39;

SELECT &＃64;i &＃61; 1

SELECT &＃64;length &＃61; DATALENGTH (&＃64;binvalue)

SELECT &＃64;hexstring &＃61; &＃39;0123456789ABCDEF&＃39;

WHILE (&＃64;i <&＃61; &＃64;length)

BEGIN

DECLARE &＃64;tempint int

DECLARE &＃64;firstint int

DECLARE &＃64;secondint int

SELECT &＃64;tempint &＃61; CONVERT(int, SUBSTRING(&＃64;binvalue,&＃64;i,1))

SELECT &＃64;firstint &＃61; FLOOR(&＃64;tempint/16)

SELECT &＃64;secondint &＃61; &＃64;tempint - (&＃64;firstint*16)

SELECT &＃64;charvalue &＃61; &＃64;charvalue &＃43;

SUBSTRING(&＃64;hexstring, &＃64;firstint&＃43;1, 1) &＃43;

SUBSTRING(&＃64;hexstring, &＃64;secondint&＃43;1, 1)

SELECT &＃64;i &＃61; &＃64;i &＃43; 1

END

SELECT &＃64;hexvalue &＃61; &＃64;charvalue

GO

CREATE PROCEDURE [dbo].[sp_help_revlogin] &＃64;login_name sysname &＃61; NULL AS

DECLARE &＃64;name sysname

DECLARE &＃64;type varchar (1)

DECLARE &＃64;hasaccess int

DECLARE &＃64;denylogin int

DECLARE &＃64;is_disabled int

DECLARE &＃64;PWD_varbinary  varbinary (256)

DECLARE &＃64;PWD_string  varchar (514)

DECLARE &＃64;SID_varbinary varbinary (85)

DECLARE &＃64;SID_string varchar (514)

DECLARE &＃64;tmpstr  varchar (1024)

DECLARE &＃64;is_policy_checked varchar (3)

DECLARE &＃64;is_expiration_checked varchar (3)

DECLARE &＃64;defaultdb sysname

IF (&＃64;login_name IS NULL)

DECLARE login_curs CURSOR FOR

SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM

sys.server_principals p LEFT JOIN sys.syslogins l

ON ( l.name &＃61; p.name ) WHERE p.type IN ( &＃39;S&＃39;, &＃39;G&＃39;, &＃39;U&＃39; ) AND p.name <> &＃39;sa&＃39;

ELSE

DECLARE login_curs CURSOR FOR

SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM

sys.server_principals p LEFT JOIN sys.syslogins l

ON ( l.name &＃61; p.name ) WHERE p.type IN ( &＃39;S&＃39;, &＃39;G&＃39;, &＃39;U&＃39; ) AND p.name &＃61; &＃64;login_name

OPEN login_curs

FETCH NEXT FROM login_curs INTO &＃64;SID_varbinary, &＃64;name, &＃64;type, &＃64;is_disabled, &＃64;defaultdb, &＃64;hasaccess, &＃64;denylogin

IF (&＃64;&＃64;fetch_status &＃61; -1)

BEGIN

PRINT &＃39;No login(s) found.&＃39;

CLOSE login_curs

DEALLOCATE login_curs

RETURN -1

END

SET &＃64;tmpstr &＃61; &＃39;/* sp_help_revlogin script &＃39;

PRINT &＃64;tmpstr

SET &＃64;tmpstr &＃61; &＃39;** Generated &＃39; &＃43; CONVERT (varchar, GETDATE()) &＃43; &＃39; on &＃39; &＃43; &＃64;&＃64;SERVERNAME &＃43; &＃39; */&＃39;

PRINT &＃64;tmpstr

PRINT &＃39;&＃39;

WHILE (&＃64;&＃64;fetch_status <> -1)

BEGIN

IF (&＃64;&＃64;fetch_status <> -2)

BEGIN

PRINT &＃39;&＃39;

SET &＃64;tmpstr &＃61; &＃39;-- Login: &＃39; &＃43; &＃64;name

PRINT &＃64;tmpstr

IF (&＃64;type IN ( &＃39;G&＃39;, &＃39;U&＃39;))

BEGIN -- NT authenticated account/group

SET &＃64;tmpstr &＃61; &＃39;CREATE LOGIN &＃39; &＃43; QUOTENAME( &＃64;name ) &＃43; &＃39; FROM WINDOWS WITH DEFAULT_DATABASE &＃61; [&＃39; &＃43; &＃64;defaultdb &＃43; &＃39;]&＃39;

END

ELSE BEGIN -- SQL Server authentication

-- obtain password and sid

SET &＃64;PWD_varbinary &＃61; CAST( LOGINPROPERTY( &＃64;name, &＃39;PasswordHash&＃39; ) AS varbinary (256) )

EXEC sp_hexadecimal &＃64;PWD_varbinary, &＃64;PWD_string OUT

EXEC sp_hexadecimal &＃64;SID_varbinary,&＃64;SID_string OUT

-- obtain password policy state

SELECT &＃64;is_policy_checked &＃61; CASE is_policy_checked WHEN 1 THEN &＃39;ON&＃39; WHEN 0 THEN &＃39;OFF&＃39; ELSE NULL END FROM sys.sql_logins WHERE name &＃61; &＃64;name

SELECT &＃64;is_expiration_checked &＃61; CASE is_expiration_checked WHEN 1 THEN &＃39;ON&＃39; WHEN 0 THEN &＃39;OFF&＃39; ELSE NULL END FROM sys.sql_logins WHERE name &＃61; &＃64;name

SET &＃64;tmpstr &＃61; &＃39;CREATE LOGIN &＃39; &＃43; QUOTENAME( &＃64;name ) &＃43; &＃39; WITH PASSWORD &＃61; &＃39; &＃43; &＃64;PWD_string &＃43; &＃39; HASHED, SID &＃61; &＃39; &＃43; &＃64;SID_string &＃43; &＃39;, DEFAULT_DATABASE &＃61; [&＃39; &＃43; &＃64;defaultdb &＃43; &＃39;]&＃39;

IF ( &＃64;is_policy_checked IS NOT NULL )

BEGIN

SET &＃64;tmpstr &＃61; &＃64;tmpstr &＃43; &＃39;, CHECK_POLICY &＃61; &＃39; &＃43; &＃64;is_policy_checked

END

IF ( &＃64;is_expiration_checked IS NOT NULL )

BEGIN

SET &＃64;tmpstr &＃61; &＃64;tmpstr &＃43; &＃39;, CHECK_EXPIRATION &＃61; &＃39; &＃43; &＃64;is_expiration_checked

END

END

IF (&＃64;denylogin &＃61; 1)

BEGIN -- login is denied access

SET &＃64;tmpstr &＃61; &＃64;tmpstr &＃43; &＃39;; DENY CONNECT SQL TO &＃39; &＃43; QUOTENAME( &＃64;name )

END

ELSE IF (&＃64;hasaccess &＃61; 0)

BEGIN -- login exists but does not have access

SET &＃64;tmpstr &＃61; &＃64;tmpstr &＃43; &＃39;; REVOKE CONNECT SQL TO &＃39; &＃43; QUOTENAME( &＃64;name )

END

IF (&＃64;is_disabled &＃61; 1)

BEGIN -- login is disabled

SET &＃64;tmpstr &＃61; &＃64;tmpstr &＃43; &＃39;; ALTER LOGIN &＃39; &＃43; QUOTENAME( &＃64;name ) &＃43; &＃39; DISABLE&＃39;

END

PRINT &＃64;tmpstr

END

FETCH NEXT FROM login_curs INTO &＃64;SID_varbinary, &＃64;name, &＃64;type, &＃64;is_disabled, &＃64;defaultdb, &＃64;hasaccess, &＃64;denylogin

END

CLOSE login_curs

DEALLOCATE login_curs

RETURN 0

GO

2> 上面建立后執行可导出所有 login和密码

EXEC sp_help_revlogin

来自 “ ITPUB博客 ” &＃xff0c;链接&＃xff1a;http://blog.itpub.net/25583515/viewspace-2146471/&＃xff0c;如需转载&＃xff0c;请注明出处&＃xff0c;否则将追究法律责任。