sp_help_revlogin導致具有Unicode字符的LOGIN上的字符丟失-有解無憂

我想匯出 SQL Server 的所有用戶，我從檔案中找到了以下存盤程序，但是當我運行程序時，一些名稱的結果只是匯出為 ??????。例如：

-- Login: ??????
IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = N'??????')
                  BEGIN
CREATE LOGIN [??????] WITH PASSWORD = 0x0200B27F654A0090DFA7C3C857709164CDC91F1 HASHED, SID = 0x27926CE54FA0E64BB2300AE2E104C22C, DEFAULT_DATABASE = [master], DEFAULT_LANGUAGE = [us_english], CHECK_POLICY = OFF, CHECK_EXPIRATION = OFF

    EXEC master.dbo.sp_addsrvrolemember @loginame='?????', @rolename='sysadmin'
END

所有 Unicode 用戶都顯示為 ?????? . SP如下：

SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
GO
  CREATE PROCEDURE [dbo].[sp_help_revlogin]   
  (
      @login_name sysname = NULL 
  )
  AS
  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 @Prefix                   VARCHAR(255)
      DECLARE @defaultdb                SYSNAME
      DECLARE @defaultlanguage          SYSNAME     
      DECLARE @tmpstrRole               VARCHAR (1024)

  IF (@login_name IS NULL)
  BEGIN
      DECLARE login_curs CURSOR 
      FOR 
          SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin, p.default_language_name  
          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'
          ORDER BY p.name
  END
  ELSE
          DECLARE login_curs CURSOR 
          FOR 
              SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin, p.default_language_name  
              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
              ORDER BY p.name

          OPEN login_curs 
          FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin, @defaultlanguage 
          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

                  SET @tmpstr='IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = N''' @name ''')
                  BEGIN'
                  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) )

                          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   ']'   ', DEFAULT_LANGUAGE = ['   @defaultlanguage   ']'

                          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
          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 

          SET @Prefix = '
          EXEC master.dbo.sp_addsrvrolemember @loginame='''

          SET @tmpstrRole=''

          SELECT @tmpstrRole = @tmpstrRole
                CASE WHEN sysadmin        = 1 THEN @Prefix   [LoginName]   ''', @rolename=''sysadmin'''        ELSE '' END
                CASE WHEN securityadmin   = 1 THEN @Prefix   [LoginName]   ''', @rolename=''securityadmin'''   ELSE '' END
                CASE WHEN serveradmin     = 1 THEN @Prefix   [LoginName]   ''', @rolename=''serveradmin'''     ELSE '' END
                CASE WHEN setupadmin      = 1 THEN @Prefix   [LoginName]   ''', @rolename=''setupadmin'''      ELSE '' END
                CASE WHEN processadmin    = 1 THEN @Prefix   [LoginName]   ''', @rolename=''processadmin'''    ELSE '' END
                CASE WHEN diskadmin       = 1 THEN @Prefix   [LoginName]   ''', @rolename=''diskadmin'''       ELSE '' END
                CASE WHEN dbcreator       = 1 THEN @Prefix   [LoginName]   ''', @rolename=''dbcreator'''       ELSE '' END
                CASE WHEN bulkadmin       = 1 THEN @Prefix   [LoginName]   ''', @rolename=''bulkadmin'''       ELSE '' END
            FROM (
                      SELECT CONVERT(VARCHAR(100),SUSER_SNAME(sid)) AS [LoginName],
                              sysadmin,
                              securityadmin,
                              serveradmin,
                              setupadmin,
                              processadmin,
                              diskadmin,
                              dbcreator,
                              bulkadmin
                      FROM sys.syslogins
                      WHERE (       sysadmin<>0
                              OR    securityadmin<>0
                              OR    serveradmin<>0
                              OR    setupadmin <>0
                              OR    processadmin <>0
                              OR    diskadmin<>0
                              OR    dbcreator<>0
                              OR    bulkadmin<>0
                          ) 
                          AND name=@name 
                ) L 
              PRINT @tmpstr
              PRINT @tmpstrRole
              PRINT 'END'
          END 
          FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin, @defaultlanguage 
      END
      CLOSE login_curs
      DEALLOCATE login_curs
      RETURN 0
  END
GO

系統中注冊為 Unicode 的用戶數量很大，手動傳輸需要很長時間。所以我需要一個可以用來正確顯示 Unicode 用戶的解決方案。

uj5u.com熱心網友回復：

我會先說通過一些工具來做這件事要好得多，而不是在 T-SQL 中拼湊一些東西。您可以使用 SSMS 或類似的。

是因為它可能：

您發布的腳本有多個問題。

您面臨的主要問題：使用 ofvarchar而不是nvarchar, 意味著其他語言會被破壞。
不必要地使用游標。這可以使用字串聚合在單個查詢中完成。
二進制到十六進制的程序完全是多余的，因為這可以通過簡單的CONVERT. 即使有必要，也可以使用行內 TVF 完成。
一些缺少QUOTENAME的用法，以正確參考名稱。
它只添加固定的服務器角色，而不是任何用戶定義的角色
它只添加CONNECT權限，而不是任何其他權限。
sys.syslogins已在 2005 年被替換的已棄用的用法。

以下腳本是完全重寫的。

它回傳基于 AD 用戶或 AD 組的所有 SQL 登錄和 Windows 登錄（但不回傳任何其他登錄，例如基于證書的登錄）。
它添加了所有服務器級別的權限。
它添加了所有服務器角色，包括用戶定義的。

CREATE OR ALTER PROCEDURE [dbo].[sp_help_revlogin]   
      @login_name sysname = NULL 
AS

SELECT
    '
/* sp_help_revlogin script
** Generated '   CONVERT(nvarchar(30), GETDATE())   ' on '   QUOTENAME(@@SERVERNAME)   ' */
GO
'   STRING_AGG(CONCAT(
    '
IF NOT EXISTS (SELECT 1 FROM sys.server_principals WHERE name = N',
    QUOTENAME(p.name, ''''),
    ')
BEGIN
    CREATE LOGIN ',
    QUOTENAME(p.name),
    CASE WHEN p.type IN ('G', 'U') THEN ' FROM WINDOWS' END,
    ' WITH',
    CASE WHEN p.type = 'S' THEN CONCAT(
        ' PASSWORD = ',
        CONVERT(nvarchar(max), ISNULL(l.password_hash, 0x0), 1),
        ' HASHED, SID = ',
        CONVERT(nvarchar(max), p.sid, 1),
        CASE WHEN l.is_policy_checked = 0 THEN ', CHECK_POLICY = OFF' END,
        CASE WHEN l.is_expiration_checked = 1 THEN ', CHECK_EXPIRATION = ON' END,
        ','
      ) END,
    ' DEFAULT_DATABASE = ',
    QUOTENAME(p.default_database_name),
    ', DEFAULT_LANGUAGE = ',
    QUOTENAME(p.default_language_name),
    ';
END;
GO
',
    CASE WHEN p.is_disabled = 1 THEN
      'ALTER LOGIN '   QUOTENAME(p.name)   ' DISABLE;
GO
'
      END,
    r.roles   '
GO
',
    sp.perms   '
GO
'
    ), N'
') WITHIN GROUP (ORDER BY p.name)

FROM sys.server_principals p
LEFT JOIN sys.sql_logins l ON l.principal_id = p.principal_id

CROSS APPLY (
    SELECT STRING_AGG(

        'EXEC sp_addsrvrolemember @loginame = '   p2.name   ', @rolename = '   QUOTENAME(r.name, '''')   ';'
      , '
GO
'
    )
    FROM sys.server_role_members rm
    JOIN sys.server_principals r ON r.principal_id = rm.role_principal_id
    CROSS APPLY (VALUES( QUOTENAME(p.name, '''') )) p2(name)  -- error 8124 
    WHERE rm.member_principal_id = p.principal_id
) r(roles)

CROSS APPLY (
    SELECT STRING_AGG(CONCAT(

        CASE WHEN sp.state = 'W' THEN N'GRANT' ELSE sp.state_desc END,
        ' ',
        sp.permission_name  COLLATE DATABASE_DEFAULT,
        ' TO ',
        p2.name,
        CASE WHEN sp.state = 'W' THEN N' WITH GRANT OPTION' END,
        ';'
      ), N'
GO
'
    )
    FROM sys.server_permissions sp
    CROSS APPLY (VALUES( QUOTENAME(p.name, '''') )) p2(name)  -- error 8124 
    WHERE sp.grantee_principal_id = p.principal_id
      AND sp.class = 100
) sp(perms)

WHERE p.type IN ('S', 'G', 'U')
  AND p.name <> 'sa'
  AND (p.name = @login_name OR @login_name IS NULL);

GO

db<>小提琴

uj5u.com熱心網友回復：

此程序很可能會解決您的問題。

USE master
GO

IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL
  DROP PROCEDURE sp_hexadecimal
GO
CREATE PROCEDURE sp_hexadecimal
    @binvalue varbinary(256),
    @hexvalue Nvarchar (514) OUTPUT
AS
DECLARE @charvalue Nvarchar (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
GO
 
IF OBJECT_ID ('sp_help_revlogin') IS NOT NULL
  DROP PROCEDURE sp_help_revlogin
GO
CREATE PROCEDURE sp_help_revlogin @login_name Nvarchar(50) = NULL AS
DECLARE @name Nvarchar(50)
DECLARE @type Nvarchar (1)
DECLARE @hasaccess int
DECLARE @denylogin int
DECLARE @is_disabled int
DECLARE @PWD_varbinary  varbinary (256)
DECLARE @PWD_string  Nvarchar (514)
DECLARE @SID_varbinary varbinary (85)
DECLARE @SID_string Nvarchar (514)
DECLARE @tmpstr  Nvarchar (1024)
DECLARE @is_policy_checked Nvarchar (3)
DECLARE @is_expiration_checked Nvarchar (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 (Nvarchar, 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) )
        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
    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
CLOSE login_curs
DEALLOCATE login_curs
RETURN 0
GO

轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/416753.html

標籤：

上一篇：使用SQL查詢如何檢查列的某個值以及其他列中的整個值？

下一篇：如何在Windows中復制SQL.mdf檔案