不清楚各位大大是怎么做数据迁移的,反正俺大老粗的做法是,先把SQL Server的数据从企业管理中备份出来然后再恢复。
在恢复的过程中经常出现的一个问题就是,数据库的原来的用户跟新的数据库系统上的登录对不上号了,以前我曾经试过多次,但是经常都忘记,总是找一个最快速的方法来恢复数据库的用户,作此记录以慰昨日之辛苦:
SQL Server 数据库恢复用户之葵花点穴手, 以下部分copy from SQL Server Books Online.
sp_change_users_login
Changes the relationship between a Microsoft® SQL Server™ login and a SQL Server user in the current database.
Syntax
sp_change_users_login [ @Action = ] 'action'
[ , [ @UserNamePattern = ] 'user' ]
[ , [ @LoginName = ] 'login' ]
Arguments
[@Action =] 'action'
Describes the action to be performed by the procedure. action is varchar(10), and can be one of these values.
Value
Description
Auto_Fix |
Links user entries in the sysusers table in the current database to logins of the same name in syslogins. It is recommended that the result from the Auto_Fix statement be checked to confirm that the links made are the intended outcome. Avoid using Auto_Fix in security-sensitive situations. Auto_Fix makes best estimates on links, possibly allowing a user more access permissions than intended.
user must be a valid user in the current database, and login must be NULL, a zero-length string (''), or not specified.
|
Report |
Lists the users, and their corresponding security identifiers (SID), that are in the current database, not linked to any login.
user and login must be NULL, a zero-length string (''), or not specified.
|
Update_One |
Links the specified user in the current database to login. login must already exist. user and login must be specified. |
[@UserNamePattern =] 'user'
Is the name of a SQL Server user in the current database. user is sysname, with a default of NULL. sp_change_users_login can be used only with the security accounts of SQL Server logins and users; it cannot be used with Microsoft Windows NT® users.
[@LoginName =] 'login'
Is the name of a SQL Server login. login is sysname, with a default of NULL.
Return Code Values
0 (success) or 1 (failure)
Result Sets
Column name
Data type
Description
UserName |
sysname |
Login name. |
UserSID |
varbinary(85) |
Login security identifier. |
Remarks
Use this procedure to link the security account for a user in the current database with a different login. If the login for a user has changed, use sp_change_users_login to link the user to the new login without losing the user's permissions.
login cannot be sa, and user cannot be the dbo, guest, or INFORMATION_SCHEMA users.
sp_change_users_login cannot be executed within a user-defined transaction.
Permissions
Any member of the public role can execute sp_change_users_login with the Report option. Only members of the sysadmin fixed server role can specify the Auto_Fix option. Only members of the sysadmin or db_owner roles can specify the Update_One option.
Examples
A. Show a report of the current user to login mappings
This example produces a report of the users in the current database and their security identifiers.
EXEC sp_change_users_login 'Report'
B. Change the login for a user
This example changes the link between user Mary in the pubs database and the existing login, to the new login NewMary (added with sp_addlogin).
--Add the new login.
USE master
go
EXEC sp_addlogin 'NewMary'
go
--Change the user account to link with the 'NewMary' login.
USE pubs
go
EXEC sp_change_users_login 'Update_One', 'Mary', 'NewMary'
<!--RELATEDTOPICSLIST-->
See Also
sp_addlogin
sp_adduser
sp_helplogins
分享到:
相关推荐
解决sqlserver数据库迁移后用户映射不上的问题
本免费小工具适用于迁移SQLServer数据库(从低版本到高版本,或者从A服务器到B服务器)。只要提前做好配置和准备,不管用户库的数据量有多大,每次迁移需要停止业务的时间都可以控制在5分钟之内(操作熟练的话,2...
Sqlserver数据库迁移,一个小项目。
sqlserver 数据库迁移工具 学习交流使用 有什么好的意见可以和我说
SQL SERVER 数据库迁移到ORACLE配置 SQL SERVER 数据库转到ORACLE配置.doc
sqlserver数据库迁移到mysql的详细方法,包含所需的工具及迁移过程中所要注意的问题
sqlserver数据库迁移mysql5.pdf
从oracle数据库迁移至SQLserver数据库,官方工具好用
数据迁移:SQL Server数据库转Mysql数据库,亲测很方便操作流程: http://blog.csdn.net/andrew_wx/article/details/6832404
此工具可以将My SQL 数据库迁移至 SQL server (迁移项目包括表及表中数据),需要注意的是此工具不支持迁移视图。需要在SQL Server 中重新手动创建视图
SQL Server和国产数据库之间数据移植研究.pdf
用java实现从SQLSERVER到ORACLE的数据库迁移
从mysql数据库迁移至sqlserver数据库,支持2005,2008版本。 text类型建议先转换为varchar(10000)类型,否则迁移后中文会出现乱码。迁移后会自动转换为ntext类型。
从sql server 迁移数据到oracle 的步骤
解密SqlServer数据库引擎是指破解SqlServer数据库引擎的加密算法,以获取数据库中的加密数据。SqlServer是一种关系型数据库管理系统,它使用数据库引擎来存储和管理数据。 内容概述: 解密SqlServer数据库引擎的...
代码如下:declare @cmd nvarchar(4000) set @cmd = N’exec [?... 您可能感兴趣的文章:如何将Oracle的一个大数据表快速迁移到 Sqlserver2008数据库(图文教程)如何把sqlserver数据迁移到mysql数据库及
将SQLServer2005数据库迁移至SQLServer2008上面
将SqlServer数据库迁移到oracle通过sqldeveloper工具进行迁移,具体的迁移步骤可以参考我博客:https://blog.csdn.net/menghuannvxia/article/details/81092429 自己已经执行成功,希望可以帮到大家
从SQL Server 2000向SQL Server 2005迁移数据库.doc
SQL Server数据库完整迁移