在Sql Server 2005中导出登录用户信息脚本

数据库 SQL Server
本文主要介绍了通过在查询分析器中执行代码的方式将登录用户信息脚本从一台机器的sql server导出到另一台机器的sql server的过程,接下来就让我们来一起学习吧。

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

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

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

 创建存储过程:

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

obtain password and sid:

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

obtain password policy state:

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

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

【编辑推荐】

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

 

责任编辑:赵鹏 来源: 博客园
相关推荐

2011-07-21 15:42:53

SQL触发器存储过程

2009-04-03 09:00:20

SQL Server2005用户

2010-07-16 14:08:39

SQL Server

2011-04-02 15:22:42

SQL server

2010-07-15 13:14:51

2009-03-30 15:52:24

数据导入导出SQL Server

2011-04-02 15:41:31

2010-07-08 12:52:58

SQL Server

2010-10-21 15:13:20

SQL Server系

2011-04-06 13:04:35

SQL Server 密码修改

2010-06-28 15:06:04

SQL Server

2009-03-16 14:01:24

正则表达式函数SQL

2010-07-23 12:55:29

SQL Server

2009-02-23 13:41:42

XML操作函数SQL Server

2011-04-02 16:20:21

SQL SERVER 同步复制

2010-07-13 16:48:14

SQL Server

2011-09-01 16:13:37

Visual StudSQL Server 存储过程

2011-08-04 18:55:53

SQL Server 用户sa登录失败

2010-07-09 11:06:13

SQL Server

2011-07-15 11:02:47

SQL Server 用户权限设置
点赞
收藏

51CTO技术栈公众号