Database A has user A, and some tables and SP created by user. During migration, the MDF and ldf in the data DIRECTORY are moved to the new MSSQL Server, and the database is restored by adding the database function.
Database A has user A, and some tables and SP created by user. During migration, the MDF and ldf in the data DIRECTORY are moved to the new MSSQL Server, and the database is restored by adding the database function.
However, user A exists in database A, while user A cannot grant permissions to user A in the new MSSQL statement. Create A new user B. The table and other information created by user A cannot be accessed. The solution is found here:
Run the following script on the source SQL Server. This script creates two stored procedures in the master database: sp_hexadecimal and sp_help_revlogin. Proceed to Step 1 after the process is created.
Note: The following process depends on the SQL Server system table. The structure of these tables may change between different versions of SQL Server. Do not select them directly from the system table.
----- Begin Script, Create sp_help_revlogin procedure -----
The Code is as follows:
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
IF OBJECT_ID ('SP _ help_revlogin ') IS NOT NULL
Drop procedure sp_help_revlogin
GO
Create procedure sp_help_revlogin @ login_name sysname = NULL
DECLARE @ name sysname
DECLARE @ xstatus int
DECLARE @ binpwd varbinary (256)
DECLARE @ txtpwd sysname
DECLARE @ tmpstr varchar (256)
DECLARE @ SID_varbinary varbinary (85)
DECLARE @ SID_string varchar (256)
IF (@ login_name is null)
DECLARE login_curs CURSOR
SELECT sid, name, xstatus, password FROM master .. sysxlogins
WHERE srvid is null and name <> 'sa'
ELSE
DECLARE login_curs CURSOR
SELECT sid, name, xstatus, password FROM master .. sysxlogins
WHERE srvid is null and name = @ login_name
OPEN login_curs
Fetch next from login_curs INTO @ SID_varbinary, @ name, @ xstatus, @ binpwd
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''
PRINT 'Clare @ pwd sysname'
WHILE (@ fetch_status <>-1)
BEGIN
IF (@ fetch_status <>-2)
BEGIN
PRINT''
SET @ tmpstr = '-- Login:' + @ name
PRINT @ tmpstr
IF (@ xstatus & 4) = 4
BEGIN -- NT authenticated account/group
IF (@ xstatus & 1) = 1
BEGIN -- NT login is denied access
SET @ tmpstr = 'exec master .. sp_denylogin ''' + @ name + ''''
PRINT @ tmpstr
END
Else begin -- NT login has access
SET @ tmpstr = 'exec master .. sp_grantlogin ''' + @ name + ''''
PRINT @ tmpstr
END
END
Else begin -- SQL Server authentication
IF (@ binpwd is not null)
BEGIN -- Non-null password
EXEC sp_hexadecimal @ binpwd, @ txtpwd OUT
IF (@ xstatus & 2048) = 2048
SET @ tmpstr = 'set @ pwd = CONVERT (varchar (256), '+ @ txtpwd + ')'
ELSE
SET @ tmpstr = 'set @ pwd = CONVERT (varbinary (256), '+ @ txtpwd + ')'
PRINT @ tmpstr
EXEC sp_hexadecimal @ SID_varbinary, @ SID_string OUT
SET @ tmpstr = 'exec master .. sp_addlogin ''' + @ name
+ ''', @ Pwd, @ sid = '+ @ SID_string +', @ encryptopt ='
END
ELSE BEGIN
-- Null password
EXEC sp_hexadecimal @ SID_varbinary, @ SID_string OUT
SET @ tmpstr = 'exec master .. sp_addlogin ''' + @ name
+ ''', NULL, @ sid = '+ @ SID_string +', @ encryptopt ='
END
IF (@ xstatus & 2048) = 2048
-- Login upgraded from 6.5
SET @ tmpstr = @ tmpstr + ''' skip _ encryption_old '''
ELSE
SET @ tmpstr = @ tmpstr + ''' skip _ encryption '''
PRINT @ tmpstr
END
END
Fetch next from login_curs INTO @ SID_varbinary, @ name, @ xstatus, @ binpwd
END
CLOSE login_curs
DEALLOCATE login_curs
RETURN 0
GO
----- End Script -----
2. After creating the sp_help_revlogin stored procedure, run the sp_help_revlogin process from the query analyzer on the source server. Sp_help_revlogin stored procedures can be used for both SQL Server 7.0 and SQL Server 2000. The output of the sp_help_revlogin stored procedure is a logon script that creates a logon with the original SID and password. Save the output, paste it to the query analyzer on the target SQL Server, and run it. Example: EXEC master .. sp_help_revlogin
The Code is as follows: SP_DEFAULTDB 'cyiyun ', 'db _ wayup'
After step 2, run sp_help_revlogin on the source server, an SQL statement is generated to create user data. For example:
The Code is as follows:
/* Sp_help_revlogin script
** Generated 06 24 2009 on WORKGROU-B1XTVC */
DECLARE @ pwd sysname
-- Login: hxtest
SET @ pwd = CONVERT (varbinary (256), CONVERT)
EXEC master .. sp_addlogin '20wa', @ pwd, @ sid = 0x1738BB6AD0CD24498F67FB5589E8EDCB, @ encryptopt = 'skip _ encryption'
......
You can solve this problem by directly running this section on the new server or finding the corresponding username to create it!
