MS-SQL 如何生出建立全服帳號的語法-搬Server時可用

2013-10-03

1596
0
2015-01-19

摘要:MS-SQL 如何生出建立全服帳號的語法-搬Server時可用

謝謝某任同事KJ的分享，握德馬(?)超好用的說
------------------------------------------------------------------------------------------------------------------------------------------------------

利用之前提過的如何查該台 Server 上，所有的 ServerLogin

這一次，我們就來看如何生出建立全服帳號的語法吧 ~~

(搬 Server 時可用，這樣就不用再煩惱密碼是多少了...^^)

這個是之前無意間從微軟的技術文章裡看來的

A 部份的，就是原文的說明，詳細的處理都在那裡面，有需要的可再慢慢研究吧 ~~

B 部份的，是我簡化一下後的語法，只挑一些常用到的參數出來用而已

不是全自動化的，不過可以 Select 出來看看語法先，然後再自己選要建的帳號 copy 出來手動跑

細節就再看了，大概提一下其中幾個比較重要的地方：

1. 出來的格式約是像這樣，有需要其它參數的，再另外去串囉 ~~

CREATE LOGIN [xxxLogin] WITH PASSWORD = 0x010056049b0ed7dc8cdba444528b3ce8fb9e04135b0df46f205e HASHED, SID = 0x3009be24d2664749b41ea97e4f6d7e02, DEFAULT_DATABASE = [master], CHECK_POLICY = OFF, CHECK_EXPIRATION = OFF

2. 用 WITH PASSWORD = 0x010056049b0ed7dc8cdba444528b3ce8fb9e04135b0df46f205e HASHED

這樣的方式來搬原來的密碼過去新 Server

3. 用 SID = 0x3009be24d2664749b41ea97e4f6d7e02

這樣的方式來把 SID 也一並搬過去，這樣就可以省下 re-mapping DbUser 的動作

若要從目前的伺服器環境搬去別台時，就可以使用。權限靠 DB 還原過去，Login 用這個方法去建，就不用再重設了

(之前 8zy 好像有提過，不過那時還沒研究出來，之後如還有需要的話再拿來用了)

4. 用 LOGINPROPERTY(xxxLoginName, 'PasswordHash')

這樣的方式來取出某 Login 的密碼(Hash 過的)

5. 用 sys.server_principals 來取 Login 的 SID 值

6. 用 CONVERT(varbinary(max), LOGINPROPERTY( name, 'PasswordHash')) AS PasswordHash_varbinary

將 sql_variant 的型態，轉成 varbinary

7. 用 sys.fn_varbintohexsubstring(1, PasswordHash_varbinary, 1, 0)

將 varbinary 的資料，轉成字串

8. 可用類似這樣的方式，去查一些系統函數的語法，如果有需要的話可幫助了解 ~~

SELECT * FROM sys.all_sql_modules AS asm WITH (NOLOCK) WHERE object_id = OBJECT_ID('sys.fn_varbintohexsubstring')

-- 開頭參數的說明像這樣

create function sys.fn_varbintohexsubstring

(

@fsetprefix bit = 1 -- append '0x' to the output

,@pbinin varbinary(max) -- input binary stream

,@startoffset int = 1 -- starting offset

,@cbytesin int = 0 -- length of input to consider, 0 means total length

)

9. 最後就是就是最早提過的，串出要的字串後，把需要的 copy 走，另開連線 F5 去執行既大功告成...^^

Microsoft 提供的詳細說明

-- How to transfer the logins and the passwords between instances of SQL Server 2005 and SQL Server 2008

-- http://support.microsoft.com/kb/918992/en-us

-- http://support.microsoft.com/kb/918992/zh-tw

-- How to transfer logins and passwords between instances of SQL Server (給舊版 2000 以前的語法)

-- http://support.microsoft.com/kb/246133/en-us

-- http://support.microsoft.com/kb/246133/zh-tw

--2 隻 sp

--1. sp_hexadecimal -- 將 varbinary 的型態，轉成 varchar

--2. sp_help_revlogin -- 生出語法

--#region /* sp_hexadecimal */

--------------------------------------------------------------------------------------------------------

USE master /* ※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※ */

IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL

DROP PROCEDURE sp_hexadecimal

CREATE PROCEDURE sp_hexadecimal

(

@binvalue varbinary(256),

@hexvalue varchar (514) OUTPUT

)

BEGIN

DECLARE @charvalue varchar (514)

DECLARE @i int

DECLARE @length int

DECLARE @hexstring char(16)

SELECT @charvalue = '0x'

SELECT @i = 1

SELECT @length = DATALENGTH (@binvalue)

SELECT @hexstring = '0123456789ABCDEF'

WHILE (@i <= @length)

BEGIN

DECLARE @tempint int

DECLARE @firstint int

DECLARE @secondint int

SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1))

SELECT @firstint = FLOOR(@tempint/16)

SELECT @secondint = @tempint - (@firstint*16)

SELECT @charvalue = @charvalue +

SUBSTRING(@hexstring, @firstint+1, 1) +

SUBSTRING(@hexstring, @secondint+1, 1)

SELECT @i = @i + 1

END

SELECT @hexvalue = @charvalue

END -- End of Procedure

--#endregion

--#region /* sp_help_revlogin */

--------------------------------------------------------------------------------------------------------

IF OBJECT_ID ('sp_help_revlogin') IS NOT NULL

DROP PROCEDURE sp_help_revlogin

CREATE PROCEDURE sp_help_revlogin

(

@login_name sysname = NULL

)

BEGIN

DECLARE @name sysname

DECLARE @type varchar (1)

DECLARE @hasaccess int

DECLARE @denylogin int

DECLARE @is_disabled int

DECLARE @PWD_varbinary varbinary (256)

DECLARE @PWD_string varchar (514)

DECLARE @SID_varbinary varbinary (85)

DECLARE @SID_string varchar (514)

DECLARE @tmpstr varchar (1024)

DECLARE @is_policy_checked varchar (3)

DECLARE @is_expiration_checked varchar (3)

DECLARE @defaultdb sysname

IF (@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 = p.name ) WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name <> 'sa'

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 = p.name ) WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name = @login_name

OPEN login_curs

FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin

IF (@@fetch_status = -1)

BEGIN

PRINT 'No login(s) found.'

CLOSE login_curs

DEALLOCATE login_curs

RETURN -1

END

SET @tmpstr = '/* sp_help_revlogin script '

PRINT @tmpstr

SET @tmpstr = '** Generated ' + CONVERT (varchar, GETDATE()) + ' on ' + @@SERVERNAME + ' */'

PRINT @tmpstr

PRINT ''

WHILE (@@fetch_status <> -1)

BEGIN

IF (@@fetch_status <> -2)

BEGIN

PRINT ''

SET @tmpstr = '-- Login:' + @name

PRINT @tmpstr

IF (@type IN ( 'G', 'U'))

BEGIN -- NT authenticated account/group

SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' FROM WINDOWS WITH DEFAULT_DATABASE = [' + @defaultdb + ']'

END

ELSE

BEGIN -- SQL Server authentication

-- obtain password and sid

SET @PWD_varbinary = CAST( LOGINPROPERTY( @name, 'PasswordHash' ) AS varbinary (256) ) -- 將 sql_variant 的型態，轉成 varbinary

EXEC sp_hexadecimal @PWD_varbinary, @PWD_string OUT

EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT

-- obtain password policy state

SELECT @is_policy_checked = CASE is_policy_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END FROM sys.sql_logins WHERE name = @name

SELECT @is_expiration_checked = CASE is_expiration_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END FROM sys.sql_logins WHERE name = @name

SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' WITH PASSWORD = ' + @PWD_string + ' HASHED, SID = ' + @SID_string + ', DEFAULT_DATABASE = [' + @defaultdb + ']'

IF ( @is_policy_checked IS NOT NULL )

BEGIN

SET @tmpstr = @tmpstr + ', CHECK_POLICY = ' + @is_policy_checked

END

IF ( @is_expiration_checked IS NOT NULL )

BEGIN

SET @tmpstr = @tmpstr + ', CHECK_EXPIRATION = ' + @is_expiration_checked

END

IF (@denylogin = 1)

BEGIN -- login is denied access

SET @tmpstr = @tmpstr + '; DENY CONNECT SQL TO ' + QUOTENAME( @name )

END

ELSE IF (@hasaccess = 0)

BEGIN -- login exists but does not have access

SET @tmpstr = @tmpstr + '; REVOKE CONNECT SQL TO ' + QUOTENAME( @name )

END

IF (@is_disabled = 1)

BEGIN -- login is disabled

SET @tmpstr = @tmpstr + '; ALTER LOGIN ' + QUOTENAME( @name ) + ' DISABLE'

END

PRINT @tmpstr

END

FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin

END -- end of WHILE

CLOSE login_curs

DEALLOCATE login_curs

RETURN 0

END -- End of Procedure

--#endregion

簡化後的語法參考

-- ---------------------------------

IF EXISTS( SELECT 1 FROM tempdb.sys.objects WHERE [type] IN ('U') AND [object_id] = OBJECT_ID('tempdb.dbo.#ZZ_______TempTable_01') )

DROP TABLE dbo.#ZZ_______TempTable_01

SELECT * INTO #ZZ_______TempTable_01

FROM

(

SELECT

CONVERT(varbinary(max), LOGINPROPERTY(name, 'PasswordHash')) AS PasswordHash_varbinary

-- ,LOGINPROPERTY( name, 'PasswordHash')

FROM sys.server_principals WITH (NOLOCK)

WHERE type IN ('S') -- 一般用的 SQL-Login

) AS x

SELECT

'CREATE LOGIN ['+name+'] WITH PASSWORD = '+sys.fn_varbintohexsubstring(1, PasswordHash_varbinary, 1, 0)+' HASHED'

+', SID = '+sys.fn_varbintohexsubstring(1, [sid], 1, 0)

+', DEFAULT_DATABASE = [master], CHECK_POLICY = OFF, CHECK_EXPIRATION = OFF' AS CreateLoginString

,'----------------->' AS [全部的欄位]

FROM #ZZ_______TempTable_01 WITH (NOLOCK)

-- WHERE xxx -- 有需要啥條件，再下吧 ~~

ORDER BY name

-- ---------------------------------

回首頁