sqlserver 登录名迁移
|
? 1.在master库中执行生成以下两个sp。 USE master GO IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL ? DROP PROCEDURE sp_hexadecimal GO CREATE PROCEDURE sp_hexadecimal ? ? @binvalue varbinary(256), ? ? @hexvalue varchar(256) OUTPUT AS DECLARE @charvalue varchar(256) 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 USE [master] ?? GO ?? /****** Object: ?StoredProcedure [dbo].[sp_help_revlogin] ?? Script Date: 07/13/2011 19:07:32 ******/ ?? SET ANSI_NULLS ON ?? GO ?? SET QUOTED_IDENTIFIER ON ?? GO ? --创建存储过程： alter PROCEDURE [dbo].[sp_help_revlogin]? @login_name sysname = NULL? AS ?? 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,'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? 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,@denylogin ?? END ?? CLOSE login_curs ?? DEALLOCATE login_curs ?? RETURN 0? 2. 执行sp_help_revlogin? exec ?sp_help_revlogin null 所有登录用户 exec?sp_help_revlogin loginname 指定登录用户 ? 孤立用户的解决方案： EXEC?sp_change_users_login?'Update_One',?'UserAccount',?'LoginAccount'; 个人理解： 用户的权限是在数据库级别控制的 ，数据库用户和登录名直接是通过sid关联的。 参考链接： http://database.51cto.com/art/201107/275918.htm ? http://support.microsoft.com/kb/246133/zh-cn （编辑：李大同） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |