在SqlServer2005中导出登录用户信息脚本

在做数据迁移时，如果需要把一个Sql Server上的用户在另外一个Sql Server上重新生成时，就可以用以下这个方法轻松解决：

玉州网站建设公司创新互联公司,玉州网站设计制作，有大型网站制作公司丰富经验。已为玉州上1000家提供企业网站建设服务。企业网站搭建\外贸网站制作要多少钱，请找那个售后服务好的玉州做网站的公司定做！

首先在查询分析器中执行以下的代码：

 
 
 
    
  
  
  
  1. USE [master]  
    2. 
  
  
  
  2.  
    3. 
  
  
  
  3. GO  
    4. 
  
  
  
  4.  
    5. 
  
  
  
  5. /****** Object:  StoredProcedure [dbo].[sp_help_revlogin]  
    6. 
  
  
  
  6.  
    7. 
  
  
  
  7. Script Date: 07/13/2011 19:07:32 ******/  
    8. 
  
  
  
  8.  
    9. 
  
  
  
  9. SET ANSI_NULLS ON  
    10. 
  
  
  
  10.  
    11. 
  
  
  
  11. GO  
    12. 
  
  
  
  12.  
    13. 
  
  
  
  13. SET QUOTED_IDENTIFIER ON  
    14. 
  
  
  
  14.  
    15. 
  
  
  
  15. GO 
    16.

 创建存储过程：

 
 
 
    
  
  
  
  1. CREATE PROCEDURE [dbo].[sp_help_revlogin] @login_name sysname = NULL AS  
    2. 
  
  
  
  2.  
    3. 
  
  
  
  3. DECLARE @name sysname  
    4. 
  
  
  
  4.  
    5. 
  
  
  
  5. DECLARE @type varchar (1)  
    6. 
  
  
  
  6.  
    7. 
  
  
  
  7. DECLARE @hasaccess int  
    8. 
  
  
  
  8.  
    9. 
  
  
  
  9. DECLARE @denylogin int  
    10. 
  
  
  
  10.  
    11. 
  
  
  
  11. DECLARE @is_disabled int  
    12. 
  
  
  
  12.  
    13. 
  
  
  
  13. DECLARE @PWD_varbinary  varbinary (256)  
    14. 
  
  
  
  14.  
    15. 
  
  
  
  15. DECLARE @PWD_string  varchar (514)  
    16. 
  
  
  
  16.  
    17. 
  
  
  
  17. DECLARE @SID_varbinary varbinary (85)  
    18. 
  
  
  
  18.  
    19. 
  
  
  
  19. DECLARE @SID_string varchar (514)  
    20. 
  
  
  
  20.  
    21. 
  
  
  
  21. DECLARE @tmpstr  varchar (1024)  
    22. 
  
  
  
  22.  
    23. 
  
  
  
  23. DECLARE @is_policy_checked varchar (3)  
    24. 
  
  
  
  24.  
    25. 
  
  
  
  25. DECLARE @is_expiration_checked varchar (3)  
    26. 
  
  
  
  26.  
    27. 
  
  
  
  27. DECLARE @defaultdb sysname  
    28. 
  
  
  
  28.  
    29. 
  
  
  
  29. IF (@login_name IS NULL)  
    30. 
  
  
  
  30.  
    31. 
  
  
  
  31. DECLARE login_curs CURSOR FOR  
    32. 
  
  
  
  32.  
    33. 
  
  
  
  33. SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM  
    34. 
  
  
  
  34.  
    35. 
  
  
  
  35. sys.server_principals p LEFT JOIN sys.syslogins l  
    36. 
  
  
  
  36.  
    37. 
  
  
  
  37. ON ( l.name = p.name ) WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name <> 'sa'  
    38. 
  
  
  
  38.  
    39. 
  
  
  
  39. ELSE  
    40. 
  
  
  
  40.  
    41. 
  
  
  
  41. DECLARE login_curs CURSOR FOR  
    42. 
  
  
  
  42.  
    43. 
  
  
  
  43. SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM  
    44. 
  
  
  
  44.  
    45. 
  
  
  
  45. sys.server_principals p LEFT JOIN sys.syslogins l  
    46. 
  
  
  
  46.  
    47. 
  
  
  
  47. ON ( l.name = p.name ) WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name = @login_name  
    48. 
  
  
  
  48.  
    49. 
  
  
  
  49. OPEN login_curs  
    50. 
  
  
  
  50.  
    51. 
  
  
  
  51. FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin  
    52. 
  
  
  
  52.  
    53. 
  
  
  
  53. IF (@@fetch_status = -1)  
    54. 
  
  
  
  54.  
    55. 
  
  
  
  55. BEGIN  
    56. 
  
  
  
  56.  
    57. 
  
  
  
  57. PRINT 'No login(s) found.'  
    58. 
  
  
  
  58.  
    59. 
  
  
  
  59. CLOSE login_curs  
    60. 
  
  
  
  60.  
    61. 
  
  
  
  61. DEALLOCATE login_curs  
    62. 
  
  
  
  62.  
    63. 
  
  
  
  63. RETURN -1  
    64. 
  
  
  
  64.  
    65. 
  
  
  
  65. END  
    66. 
  
  
  
  66.  
    67. 
  
  
  
  67. SET @tmpstr = '/* sp_help_revlogin script ' 
    68. 
  
  
  
  68.  
    69. 
  
  
  
  69. PRINT @tmpstr  
    70. 
  
  
  
  70.  
    71. 
  
  
  
  71. SET @tmpstr = '** Generated ' + CONVERT (varchar, GETDATE()) + ' on ' + @@SERVERNAME + ' */'  
    72. 
  
  
  
  72.  
    73. 
  
  
  
  73. PRINT @tmpstr  
    74. 
  
  
  
  74.  
    75. 
  
  
  
  75. PRINT ''  
    76. 
  
  
  
  76.  
    77. 
  
  
  
  77. WHILE (@@fetch_status <> -1)  
    78. 
  
  
  
  78.  
    79. 
  
  
  
  79. BEGIN  
    80. 
  
  
  
  80.  
    81. 
  
  
  
  81. IF (@@fetch_status <> -2)  
    82. 
  
  
  
  82.  
    83. 
  
  
  
  83. BEGIN  
    84. 
  
  
  
  84.  
    85. 
  
  
  
  85. PRINT ''  
    86. 
  
  
  
  86.  
    87. 
  
  
  
  87. SET @tmpstr = '-- Login: ' + @name  
    88. 
  
  
  
  88.  
    89. 
  
  
  
  89. PRINT @tmpstr  
    90. 
  
  
  
  90.  
    91. 
  
  
  
  91. IF (@type IN ( 'G', 'U'))  
    92. 
  
  
  
  92.  
    93. 
  
  
  
  93. BEGIN -- NT authenticated account/group  
    94. 
  
  
  
  94.  
    95. 
  
  
  
  95. SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' FROM WINDOWS WITH DEFAULT_DATABASE = [' + @defaultdb + ']'  
    96. 
  
  
  
  96.  
    97. 
  
  
  
  97. END  
    98. 
  
  
  
  98.  
    99. 
  
  
  
  99. ELSE BEGIN -- SQL Server authentication 
    100.

obtain password and sid：

 
 
 
    
  
  
  
  1. SET @PWD_varbinary = CAST( LOGINPROPERTY( @name, 'PasswordHash' ) AS varbinary (256) )  
    2. 
  
  
  
  2.  
    3. 
  
  
  
  3. EXEC sp_hexadecimal @PWD_varbinary, @PWD_string OUT  
    4. 
  
  
  
  4.  
    5. 
  
  
  
  5. EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT 
    6.

obtain password policy state：

 
 
 
    
  
  
  
  1. SELECT @is_policy_checked = CASE is_policy_checked WHEN 1 THEN 'ON' WHEN
    2. 
  
  
  
  2.  0 THEN 'OFF' ELSE NULL END FROM sys.sql_logins WHERE name = @name  
    3. 
  
  
  
  3.  
    4. 
  
  
  
  4. SELECT @is_expiration_checked = CASE is_expiration_checked WHEN 1 THEN 'ON' WHEN
    5. 
  
  
  
  5.  0 THEN 'OFF' ELSE NULL END FROM sys.sql_logins WHERE name = @name  
    6. 
  
  
  
  6.  
    7. 
  
  
  
  7. SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' WITH PASSWORD = ' + @PWD_string + ' HASHED, 
    8. 
  
  
  
  8. SID = ' + @SID_string + ', DEFAULT_DATABASE = [' + @defaultdb + ']'  
    9. 
  
  
  
  9.  
    10. 
  
  
  
  10. IF ( @is_policy_checked IS NOT NULL )  
    11. 
  
  
  
  11.  
    12. 
  
  
  
  12. BEGIN  
    13. 
  
  
  
  13.  
    14. 
  
  
  
  14. SET @tmpstr = @tmpstr + ', CHECK_POLICY = ' + @is_policy_checked  
    15. 
  
  
  
  15.  
    16. 
  
  
  
  16. END  
    17. 
  
  
  
  17.  
    18. 
  
  
  
  18. IF ( @is_expiration_checked IS NOT NULL )  
    19. 
  
  
  
  19.  
    20. 
  
  
  
  20. BEGIN  
    21. 
  
  
  
  21.  
    22. 
  
  
  
  22. SET @tmpstr = @tmpstr + ', CHECK_EXPIRATION = ' + @is_expiration_checked  
    23. 
  
  
  
  23.  
    24. 
  
  
  
  24. END  
    25. 
  
  
  
  25.  
    26. 
  
  
  
  26. END  
    27. 
  
  
  
  27.  
    28. 
  
  
  
  28. IF (@denylogin = 1)  
    29. 
  
  
  
  29.  
    30. 
  
  
  
  30. BEGIN -- login is denied access  
    31. 
  
  
  
  31.  
    32. 
  
  
  
  32. SET @tmpstr = @tmpstr + '; DENY CONNECT SQL TO ' + QUOTENAME( @name )  
    33. 
  
  
  
  33.  
    34. 
  
  
  
  34. END  
    35. 
  
  
  
  35.  
    36. 
  
  
  
  36. ELSE IF (@hasaccess = 0)  
    37. 
  
  
  
  37.  
    38. 
  
  
  
  38. BEGIN -- login exists but does not have access  
    39. 
  
  
  
  39.  
    40. 
  
  
  
  40. SET @tmpstr = @tmpstr + '; REVOKE CONNECT SQL TO ' + QUOTENAME( @name )  
    41. 
  
  
  
  41.  
    42. 
  
  
  
  42. END  
    43. 
  
  
  
  43.  
    44. 
  
  
  
  44. IF (@is_disabled = 1)  
    45. 
  
  
  
  45.  
    46. 
  
  
  
  46. BEGIN -- login is disabled  
    47. 
  
  
  
  47.  
    48. 
  
  
  
  48. SET @tmpstr = @tmpstr + '; ALTER LOGIN ' + QUOTENAME( @name ) + ' DISABLE'  
    49. 
  
  
  
  49.  
    50. 
  
  
  
  50. END  
    51. 
  
  
  
  51.  
    52. 
  
  
  
  52. PRINT @tmpstr  
    53. 
  
  
  
  53.  
    54. 
  
  
  
  54. END  
    55. 
  
  
  
  55.  
    56. 
  
  
  
  56. FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin  
    57. 
  
  
  
  57.  
    58. 
  
  
  
  58. END  
    59. 
  
  
  
  59.  
    60. 
  
  
  
  60. CLOSE login_curs  
    61. 
  
  
  
  61.  
    62. 
  
  
  
  62. DEALLOCATE login_curs  
    63. 
  
  
  
  63.  
    64. 
  
  
  
  64. RETURN 0 
    65.

 执行完以上的代码，登录用户信息的脚本就导出成功了。本篇就介绍到这里，文中若有不妥之处，欢迎大家批评指正。

【编辑推荐】

  1. 巧用DAC解决SQL Server登录失败的问题
  2. 如何将系统监视器数据记录到SQL Server
  3. 用FOR XML PATH将查询结果以XML输出
  4. 使用SQL Trace来实现SQL Server的跟踪操作
  5. CTE和WITH AS短语结合使用提高SQL查询性能

当前标题：在SqlServer2005中导出登录用户信息脚本
浏览路径：http://www.mswzjz.com/qtweb/news46/189946.html

网站建设、网络推广公司-创新互联，是专注品牌与效果的网站制作，网络营销seo公司；服务项目有等

广告

声明：本网站发布的内容（图片、视频和文字）以用户投稿、用户转载内容为主，如果涉及侵权请尽快告知，我们将会在第一时间删除。文章观点不代表本网站立场，如需处理请联系客服。电话：028-86922220；邮箱：631063699@qq.com。内容未经允许不得转载，或转载时需注明来源： 创新互联

猜你还喜欢下面的内容

网站收录知识

行业网站建设

成都网站建设公司　　 　香港云主机　　 　成都logo设计标志　　 　成都广告招牌制作　　 　手机网站制作　　 　seo关键词优化　　 　免备案空间腾讯云　　 　成都活动策划　　 　抖音短视频拍摄　　 　红光高低压开关厂　　 　兴久牧业　　 　上海除甲醛　　 　中国（西部）云计算中心　　 　SSL证书　　 　成都主机托管　　 　正泰动物　　 　达州做网站　　 　网站推广　　 　德阳机房　　 　消防工程