更多_SQLServer中登录账号与数据库用户迁移

篇首语：本文由编程笔记#小编为大家整理，主要介绍了SQL Server 中登录账号与数据库用户迁移相关的知识，希望对你有一定的参考价值。

1.      先创建一个SqlServer身份验证的登录名&＃xff0c;并映射到数据库中。

如&＃xff1a;创建用户 [kk] 映射到数据库 [mytest]&＃xff0c;此时数据库 [mytest]会增加一个用户 [kk]

2.      此时再删除登录名 [kk]&＃xff0c;删除后&＃xff0c;数据库[mytest]将存在一个孤立用户 [kk]

3.      查看当前数据库中是否存在孤立用户

use mytest;
exec sp_change_users_login &＃64;Action&＃61;&＃39;Report&＃39;;


4.      对于孤立用户&＃xff0c;有两种情况&＃xff1a;

a 不知道这些数据库用户之前的登录名或记不清楚数据库来源

b 对于数据库迁移一种情况&＃xff0c;先迁移数据库&＃xff0c;再迁移登录账号

第一种情况&＃xff1a;

-- 创建登录名
use master;
create login [kk] with password &＃61; &＃39;123456&＃39;;
go
-- 对孤立用户连接到现有的登录名
use mytest;
exec sp_change_users_login
&＃64;action&＃61;&＃39;update_one&＃39;,
&＃64;usernamepattern&＃61;&＃39;kk&＃39;, --数据库孤立用户
&＃64;loginname&＃61;&＃39;kk&＃39;; --关联到sql server登录名
go
-- 也可以再次修改密码
use master
go
sp_password &＃64;old&＃61;null, &＃64;new&＃61;&＃39;654321&＃39;, &＃64;loginame&＃61;&＃39;kk&＃39;;
go


第二种情况&＃xff1a;参考 如何在 SQL Server 2005 实例之间传输登录和密码

如&＃xff1a;要创建与另一个数据库结构一样的数据库&＃xff0c;可以导出脚本到另一台服务器中执行&＃xff08;其中有数据库用户&＃xff09;

注意&＃xff1a;数据库用户的权限并没有随之授予&＃xff0c;须手动再次授予权限&＃xff01;

在原数据库中创建存储过程&＃xff1a;

USE master
GO
IF OBJECT_ID (&＃39;sp_hexadecimal&＃39;) IS NOT NULL
DROP PROCEDURE sp_hexadecimal
GO
CREATE PROCEDURE sp_hexadecimal
&＃64;binvalue varbinary(256),
&＃64;hexvalue varchar (514) OUTPUT
AS
DECLARE &＃64;charvalue varchar (514)
DECLARE &＃64;i int
DECLARE &＃64;length int
DECLARE &＃64;hexstring char(16)
SELECT &＃64;charvalue &＃61; &＃39;0x&＃39;
SELECT &＃64;i &＃61; 1
SELECT &＃64;length &＃61; DATALENGTH (&＃64;binvalue)
SELECT &＃64;hexstring &＃61; &＃39;0123456789ABCDEF&＃39;
WHILE (&＃64;i <&＃61; &＃64;length)
BEGIN
DECLARE &＃64;tempint int
DECLARE &＃64;firstint int
DECLARE &＃64;secondint int
SELECT &＃64;tempint &＃61; CONVERT(int, SUBSTRING(&＃64;binvalue,&＃64;i,1))
SELECT &＃64;firstint &＃61; FLOOR(&＃64;tempint/16)
SELECT &＃64;secondint &＃61; &＃64;tempint - (&＃64;firstint*16)
SELECT &＃64;charvalue &＃61; &＃64;charvalue &＃43;
SUBSTRING(&＃64;hexstring, &＃64;firstint&＃43;1, 1) &＃43;
SUBSTRING(&＃64;hexstring, &＃64;secondint&＃43;1, 1)
SELECT &＃64;i &＃61; &＃64;i &＃43; 1
END
SELECT &＃64;hexvalue &＃61; &＃64;charvalue
GO

IF OBJECT_ID (&＃39;sp_help_revlogin&＃39;) IS NOT NULL
DROP PROCEDURE sp_help_revlogin
GO
CREATE PROCEDURE sp_help_revlogin &＃64;login_name sysname &＃61; NULL AS
DECLARE &＃64;name sysname
DECLARE &＃64;type varchar (1)
DECLARE &＃64;hasaccess int
DECLARE &＃64;denylogin int
DECLARE &＃64;is_disabled int
DECLARE &＃64;PWD_varbinary varbinary (256)
DECLARE &＃64;PWD_string varchar (514)
DECLARE &＃64;SID_varbinary varbinary (85)
DECLARE &＃64;SID_string varchar (514)
DECLARE &＃64;tmpstr varchar (1024)
DECLARE &＃64;is_policy_checked varchar (3)
DECLARE &＃64;is_expiration_checked varchar (3)
DECLARE &＃64;defaultdb sysname

IF (&＃64;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 &＃61; p.name ) WHERE p.type IN ( &＃39;S&＃39;, &＃39;G&＃39;, &＃39;U&＃39; ) AND p.name <> &＃39;sa&＃39;
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 &＃61; p.name ) WHERE p.type IN ( &＃39;S&＃39;, &＃39;G&＃39;, &＃39;U&＃39; ) AND p.name &＃61; &＃64;login_name
OPEN login_curs
FETCH NEXT FROM login_curs INTO &＃64;SID_varbinary, &＃64;name, &＃64;type, &＃64;is_disabled, &＃64;defaultdb, &＃64;hasaccess, &＃64;denylogin
IF (&＃64;&＃64;fetch_status &＃61; -1)
BEGIN
PRINT &＃39;No login(s) found.&＃39;
CLOSE login_curs
DEALLOCATE login_curs
RETURN -1
END
SET &＃64;tmpstr &＃61; &＃39;/* sp_help_revlogin script &＃39;
PRINT &＃64;tmpstr
SET &＃64;tmpstr &＃61; &＃39;** Generated &＃39; &＃43; CONVERT (varchar, GETDATE()) &＃43; &＃39; on &＃39; &＃43; &＃64;&＃64;SERVERNAME &＃43; &＃39; */&＃39;
PRINT &＃64;tmpstr
PRINT &＃39;&＃39;
WHILE (&＃64;&＃64;fetch_status <> -1)
BEGIN
IF (&＃64;&＃64;fetch_status <> -2)
BEGIN
PRINT &＃39;&＃39;
SET &＃64;tmpstr &＃61; &＃39;-- Login: &＃39; &＃43; &＃64;name
PRINT &＃64;tmpstr
IF (&＃64;type IN ( &＃39;G&＃39;, &＃39;U&＃39;))
BEGIN -- NT authenticated account/group
SET &＃64;tmpstr &＃61; &＃39;CREATE LOGIN &＃39; &＃43; QUOTENAME( &＃64;name ) &＃43; &＃39; FROM WINDOWS WITH DEFAULT_DATABASE &＃61; [&＃39; &＃43; &＃64;defaultdb &＃43; &＃39;]&＃39;
END
ELSE BEGIN -- SQL Server authentication
-- obtain password and sid
SET &＃64;PWD_varbinary &＃61; CAST( LOGINPROPERTY( &＃64;name, &＃39;PasswordHash&＃39; ) AS varbinary (256) )
EXEC sp_hexadecimal &＃64;PWD_varbinary, &＃64;PWD_string OUT
EXEC sp_hexadecimal &＃64;SID_varbinary,&＃64;SID_string OUT

-- obtain password policy state
SELECT &＃64;is_policy_checked &＃61; CASE is_policy_checked WHEN 1 THEN &＃39;ON&＃39; WHEN 0 THEN &＃39;OFF&＃39; ELSE NULL END FROM sys.sql_logins WHERE name &＃61; &＃64;name
SELECT &＃64;is_expiration_checked &＃61; CASE is_expiration_checked WHEN 1 THEN &＃39;ON&＃39; WHEN 0 THEN &＃39;OFF&＃39; ELSE NULL END FROM sys.sql_logins WHERE name &＃61; &＃64;name

SET &＃64;tmpstr &＃61; &＃39;CREATE LOGIN &＃39; &＃43; QUOTENAME( &＃64;name ) &＃43; &＃39; WITH PASSWORD &＃61; &＃39; &＃43; &＃64;PWD_string &＃43; &＃39; HASHED, SID &＃61; &＃39; &＃43; &＃64;SID_string &＃43; &＃39;, DEFAULT_DATABASE &＃61; [&＃39; &＃43; &＃64;defaultdb &＃43; &＃39;]&＃39;
IF ( &＃64;is_policy_checked IS NOT NULL )
BEGIN
SET &＃64;tmpstr &＃61; &＃64;tmpstr &＃43; &＃39;, CHECK_POLICY &＃61; &＃39; &＃43; &＃64;is_policy_checked
END
IF ( &＃64;is_expiration_checked IS NOT NULL )
BEGIN
SET &＃64;tmpstr &＃61; &＃64;tmpstr &＃43; &＃39;, CHECK_EXPIRATION &＃61; &＃39; &＃43; &＃64;is_expiration_checked
END
END
IF (&＃64;denylogin &＃61; 1)
BEGIN -- login is denied access
SET &＃64;tmpstr &＃61; &＃64;tmpstr &＃43; &＃39;; DENY CONNECT SQL TO &＃39; &＃43; QUOTENAME( &＃64;name )
END
ELSE IF (&＃64;hasaccess &＃61; 0)
BEGIN -- login exists but does not have access
SET &＃64;tmpstr &＃61; &＃64;tmpstr &＃43; &＃39;; REVOKE CONNECT SQL TO &＃39; &＃43; QUOTENAME( &＃64;name )
END
IF (&＃64;is_disabled &＃61; 1)
BEGIN -- login is disabled
SET &＃64;tmpstr &＃61; &＃64;tmpstr &＃43; &＃39;; ALTER LOGIN &＃39; &＃43; QUOTENAME( &＃64;name ) &＃43; &＃39; DISABLE&＃39;
END
PRINT &＃64;tmpstr
END
FETCH NEXT FROM login_curs INTO &＃64;SID_varbinary, &＃64;name, &＃64;type, &＃64;is_disabled, &＃64;defaultdb, &＃64;hasaccess, &＃64;denylogin
END
CLOSE login_curs
DEALLOCATE login_curs
RETURN 0
GO

执行存储过程&＃xff0c;将生成登录名的创建脚本

EXEC sp_help_revlogin

将脚本拷贝到当前数据库孤立用户的服务器中执行&＃xff08;更多注意问题参考如何在 SQL Server 2005 实例之间传输登录和密码&＃xff09;

CREATE LOGIN [kk]
WITH
PASSWORD &＃61; 0x0100FDBC7416947C56E903E945B5DF891643064BB7D16381577F HASHED,
SID &＃61; 0xAE142AE3C75E9341B106B9BAA60BB0CC,
DEFAULT_DATABASE &＃61; [mytest],
CHECK_POLICY &＃61; OFF,
CHECK_EXPIRATION &＃61; OFF


虽然登录名和数据库用户已经存在了&＃xff0c;但是原来数据库用户的角色身份、对象权限都没有了&＃xff01;

这时&＃xff0c;在原数据库执行以下脚本&＃xff0c;拷贝脚本到新的数据库中执行&＃xff0c;将权限还原

&＃xff08;授予数据库孤立用户权限或者其他所有用户的权限都“迁移”过来&＃xff09;

-- 更改【此用户拥有的架构】
select s.name,p.name,&＃39;ALTER AUTHORIZATION ON SCHEMA::[&＃39;&＃43;s.name&＃43;&＃39;] TO [&＃39;&＃43;p.name&＃43;&＃39;]&＃39;
from sys.schemas s inner join sys.database_principals p on s.principal_id&＃61;p.principal_id
where s.name <> p.name
-- 授予【数据库角色成员身份】权限
SELECT &＃39;exec sp_addrolemember N&＃39;&＃39;&＃39;&＃43;g.name&＃43;&＃39;&＃39;&＃39;, N&＃39;&＃39;&＃39;&＃43;u.name&＃43;&＃39;&＃39;&＃39;&＃39;
FROM sys.database_principals u
inner join sys.database_role_members m on u.principal_id &＃61; m.member_principal_id
inner join sys.database_principals g on g.principal_id &＃61; m.role_principal_id
ORDER BY g.name,u.name
-- 授予【安全对象】权限
SELECT N&＃39;grant &＃39;&＃43;B.permission_name collate chinese_prc_ci_ai_ws&＃43;N&＃39; on [&＃39;&＃43;A.name&＃43;N&＃39;] to [&＃39;&＃43;C.name&＃43;N&＃39;]&＃39;
FROM sys.sysobjects A(NOLOCK)
INNER JOIN sys.database_permissions B(NOLOCK) ON A.id&＃61;B.major_id
INNER JOIN sys.database_principals C(NOLOCK) ON B.grantee_principal_id&＃61;C.principal_id
--WHERE C.name&＃61;&＃39;kk&＃39; --A.name&＃61;&＃39;objectName&＃39;


-- 程序集权限查询及授予
SELECT * FROM sys.types WHERE is_user_defined&＃61;1
SELECT * FROM sys.table_types
SELECT pms.state_desc,pms.permission_name,pms.class_desc,stt.name,tt.name,psp.name
,pms.state_desc&＃43;&＃39; &＃39;&＃43;pms.permission_name&＃43;&＃39; ON &＃39;&＃43;class_desc&＃43;&＃39;::[&＃39;&＃43;stt.name&＃43;&＃39;].[&＃39;&＃43;tt.name&＃43;&＃39;] TO [&＃39;&＃43;psp.name&＃43;&＃39;]&＃39;
collate Chinese_PRC_Stroke_CI_AS
FROM sys.table_types AS tt
INNER JOIN sys.schemas AS stt ON stt.schema_id &＃61; tt.schema_id
INNER JOIN sys.database_permissions AS pms ON pms.major_id&＃61;tt.user_type_id
INNER JOIN sys.database_principals AS psp ON psp.principal_id &＃61; pms.grantee_principal_id
WHERE pms.class&＃61;6 AND pms.minor_id&＃61;0 AND pms.state &＃61; &＃39;G&＃39;

如果【数据库用户】还有对其他对象有操作权限&＃xff0c;另外授予&＃xff08;当前不列出所有权限&＃xff09;

至此&＃xff0c;完成账号迁移&＃xff0c;步骤如下&＃xff1a;

1 数据库迁移&＃xff0c;产生孤立用户

2 登录账号迁移&＃xff0c;关联孤立用户

3 数据库用户权限迁移&＃xff0c;所有操作权限重新授予

参考&＃xff1a;

如何在SQLServer 2005实例之间传输登录和密码

孤立用户故障排除(SQLServer)