Migration of login accounts and database users in SQL Server, SQL Server

Migration of login accounts and database users in SQL Server, SQL Server

1. Create a login name for SQL Server Authentication and map it to the database.

For example, if you create a user [kk] To map to the database [mytest], the database [mytest] will add a user [kk].

2. Delete the logon name [kk]. After deletion, the database [mytest] will have an isolated user [kk].

3. check whether there are isolated users in the current database.

use mytest;exec sp_change_users_login @Action='Report';

4. There are two scenarios for isolated users:

A does not know the login names of these database users or does not know the database Source

B. For database migration, first migrate the database and then migrate the login account

 

First case:

-- Create the login name use master; create login [kk] with password = '000000'; go -- connect the isolated user to the existing login name use mytest; exec sp_change_users_login @ action = 'Update _ one', @ usernamepattern = 'kk ', -- database isolated user @ loginname = 'kk '; -- associate with the SQL server login name go -- you can also change the password again to use master gosp_password @ old = null, @ new = '000000', @ loginame = 'kk '; go

Case 2:Refer to how to transfer logon and password between SQL Server 2005 instances

 

For example, to create a database with the same structure as another database, you can export the script to another server for execution (including database users)

Note: the permissions of database users are not granted. You must manually grant the permissions again!

Create a stored procedure in the original database:

USE masterGOIF OBJECT_ID ('sp_hexadecimal') IS NOT NULL  DROP PROCEDURE sp_hexadecimalGOCREATE PROCEDURE sp_hexadecimal    @binvalue varbinary(256),    @hexvalue varchar (514) OUTPUTASDECLARE @charvalue varchar (514)DECLARE @i intDECLARE @length intDECLARE @hexstring char(16)SELECT @charvalue = '0x'SELECT @i = 1SELECT @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 + 1ENDSELECT @hexvalue = @charvalueGO IF OBJECT_ID ('sp_help_revlogin') IS NOT NULL  DROP PROCEDURE sp_help_revloginGOCREATE PROCEDURE sp_help_revlogin @login_name sysname = NULL ASDECLARE @name sysnameDECLARE @type varchar (1)DECLARE @hasaccess intDECLARE @denylogin intDECLARE @is_disabled intDECLARE @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_nameOPEN login_cursFETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denyloginIF (@@fetch_status = -1)BEGIN  PRINT 'No login(s) found.'  CLOSE login_curs  DEALLOCATE login_curs  RETURN -1ENDSET @tmpstr = '/* sp_help_revlogin script 'PRINT @tmpstrSET @tmpstr = '** Generated ' + CONVERT (varchar, GETDATE()) + ' on ' + @@SERVERNAME + ' */'PRINT @tmpstrPRINT ''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   ENDCLOSE login_cursDEALLOCATE login_cursRETURN 0GO

Execute the stored procedure and generate the Login Name Creation script

EXEC sp_help_revlogin

Copy the script to the Server where the current database is isolated (for more information, see how to transfer the login and password between SQL Server 2005 instances)

CREATE LOGIN [kk] WITH PASSWORD = 0x0100FDBC7416947C56E903E945B5DF891643064BB7D16381577F HASHED, SID = 0xAE142AE3C75E9341B106B9BAA60BB0CC, DEFAULT_DATABASE = [mytest], CHECK_POLICY = OFF, CHECK_EXPIRATION = OFF

Although the login name and Database User already exist, the role identity and object permissions of the original database user are no longer available!

 

In this case, execute the following script in the original database, copy the script to the new database, and restore the permission.

(Grant the isolated database user permission or all other user permissions to "migrate)

-- Change [this user's architecture] select s. name, p. name, 'alter authorization on schema: ['+ s. name + '] TO [' + p. name + ']' from sys. schemas s inner join sys. database_principals p on s. principal_id = p. principal_idwhere s. name <> p. name -- Grant the [database role member identity] Permission to SELECT 'exec sp_addrolemember n''' + g. name + ''', n''' + u. name + ''' FROM sys. database_principals uinner join sys. database_role_members m on u. principal_id = m. member_principal_idinner join sys. database_principals g on g. principal_id = m. role_principal_idORDER BY g. name, u. name -- grant [security object] Permission to select n 'Grant '+ B. permission_name collate chinese_prc_ci_ai_ws + N' on ['+. name + N'] to ['+ C. name + N'] 'FROM sys. sysobjects A (NOLOCK) inner join sys. database_permissions B (NOLOCK) ON. id = B. major_id inner join sys. database_principals C (NOLOCK) on B. grantee_principal_id = C. principal_id -- where c. name = 'kk '--. name = 'objectname'

If the [database user] Has operation permissions on other objects, it also grants (not all permissions are listed currently)

So far, the account migration is completed as follows:

1. database migration generates isolated users

2. migrate login accounts and associate them with isolated users

3. migrate database user permissions, and re-grant all operation Permissions

 

 

Refer:

How to transmit logon and password between SQL Server 2005 instances

Isolated user troubleshooting (SQLServer)