Log on account in SQL Server and database user migration

1. First create a login for SQL Server authentication and map to the database.

such as: Create User [KK] mapping to a database [MyTest] , this time the database [MyTest] will add a user [KK]

2. Delete the login name at this time [KK], after deletion, the database [mytest] will exist an orphaned user [KK]

3. See if there are orphaned users in the current database

Use mytest;exec sp_change_users_login @Action = ' report ';

4. for orphaned users, there are two scenarios:

a do not know the login name of these database users or remember the database source

b In the case of a database migration, migrate the database before migrating the login account

First case:

--Create login use master;create login [KK] with password = ' 123456 '; go--connect to an orphaned user to an existing login with mytest;exec sp_change_users_login @ Action= ' Update_One ', @usernamepattern = ' kk ',--database orphaned user @loginname= ' KK '; --associated to SQL Server login go--can also change the password again use master Gosp_password @old =null, @new = ' 654321 ', @loginame = ' kk '; go

second case: refer to how to transfer logins and passwords between instances of SQL Server 2005

For example, to create a database that is the same as another database structure, you can export the script to execute on another server (where there are database users)

Note: The permissions of the database user are not granted and must be manually granted again!

Use Mastergoif object_id (' sp_hexadecimal ') are not NULL DROP PROCEDURE sp_hexadecimalgocreate PROCEDURE sp_hexadecimal @binvalue varbinary, @hexvalue varchar (514) outputasdeclare @charvalue varchar (514) DECLARE @i intdeclare @lengt H intDECLARE @hexstring char (+) Select @charvalue = ' 0x ' select @i = 1SELECT @length = datalength (@binvalue) Select @hexstr   ing = ' 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 = @t Empint-(@firstint *16) SELECT @charvalue = @charvalue + SUBSTRING (@hexstring, @firstint +1, 1) + SUBSTRING (@hexstri  Ng, @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 sysnamedecl is @type varchar (1) DECLis @hasaccess intdeclare @denylogin intdeclare @is_disabled intdeclare @PWD_varbinary varbinary (DECLARE) @PWD_strin G varchar (514) DECLARE @SID_varbinary varbinary () DECLARE @SID_string varchar (514) DECLARE @tmpstr varchar (1024x768) DECLA RE @is_policy_checked varchar (3) DECLARE @is_expiration_checked varchar (3) DECLARE @defaultdb sysname IF (@login_name is N ULL) 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 joins 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.D Efault_database_name, L.hasaccess, l.denylogin from Sys.server_principals P left joins 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 to @SID_varb Inary, @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 @tmps  TR 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 Varbinar  Y ()) 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 If 1 then ' on ' while 0 Then ' OFF ' ELSE NULL END from Sys.sql_ Logins WHERE name = @name SELECT @is_expiration_checked = case is_expiration_checked If 1 then ' on ' while 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_check Ed END IF (@is_expiration_checked is not NULL) BEGIN SET @tmpstr = @tmpstr + ', CHECK_EXPI ration = ' + @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 has a CcESS SET @tmpstr = @tmpstr + '; REVOKE CONNECT SQL to ' + QUOTENAME (@name) END IF (@is_disabled = 1) BEGIN – login is disabled SET @tmpst r = @tmpstr + '; ALTER LOGIN ' + QUOTENAME (@name) + ' DISABLE ' end PRINT @tmpstr end FETCH NEXT from login_curs into @SID_varbina Ry, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin endclose login_cursdeallocate Login_cursreturn 0GO

Executes a stored procedure that generates a logon name creation script

EXEC sp_help_revlogin

Copy the script to the current database orphaned user's server execution (more attention issues reference How to transfer logins and passwords between instances of SQL Server 2005 )

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, but the original database user role identity, object permissions are not!

At this point, execute the following script in the original database, copy the script to the new database, and restore the permissions

(Grant database orphaned user rights or all other users ' permissions are "migrated".)

--Change "schema owned by this user" 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 database role membership Permissions 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_p Rincipal_idorder by g.name,u.name--Grant "securable" right Select N ' Grant ' +b.permission_name  collate chinese_prc_ci_ai_ws+n ' On [' +a.name+n '] to [' +c.name+n '] ' from Sys.sysobjects A (NOLOCK) INNER joins Sys.database_permissions B (NOLOCK) on A.ID=B.M ajor_id INNER JOIN sys.database_principals C (NOLOCK) on B.grantee_principal_id=c.principal_id--where c.name= ' KK '-- A.name= ' ObjectName '

If the database user also has permission to operate on other objects, grant (all permissions are not currently listed)

Now, complete the account migration process as follows:

1 database migration, creating orphaned users

2 login account Migration, associated orphaned users

3 Database User Rights Migration, all operation permissions re-granted

Reference:

how the SQL transfer Logins and passwords between Server 2005 Instances

Troubleshooting Orphaned users (sqlServer)

Log on account in SQL Server and database user migration