I'm having the issue with the user permission in my new database. I copied this new database from different sever, that old server database has some users,granted permission to execute some procedures. after i backed up and restored in to new server all permissions were gone. I run sp_helprotect to find out the users and permissions from my old server. Is there is any way to script those permissions and roles?
我的新数据库中存在用户权限问题。我从不同的服务器复制了这个新的数据库，旧的服务器数据库有一些用户，允许执行一些过程。在我备份并恢复到新的服务器之后，所有的权限都消失了。我运行sp_helprotect来查找旧服务器的用户和权限。是否有任何方法可以编写这些权限和角色?
We are running the SQL server 2012 version now. but that old db was in SQL Server 2008 r2. is that causing the problem?
我们现在正在运行SQL server 2012版本。但是旧的db在SQL Server 2008 r2中。这是问题的原因吗?
2 个解决方案
#1
4
When you restored your database to a new server, even if the same users exist in master, they don't have the same system ids, so you have to drop the users from your restored database and add them to your restored database again. This way, the authenticated user's system ids flow through from master to your database and the rights are all restored without having to re-script all the rights...
当您将数据库还原到新服务器时，即使在master中存在相同的用户，它们也没有相同的系统id，因此您必须将用户从恢复的数据库中删除，并将其再次添加到恢复的数据库中。通过这种方式，经过身份验证的用户的系统id从主服务器流到数据库，所有权限都被恢复，而无需重新编写所有权限……
For instance, when I restored my db to another server, I had to execute the following script for each login that we use:
例如，当我将我的数据库恢复到另一个服务器时，我必须为我们使用的每个登录执行以下脚本:
USE [new_db_name]
GO
IF EXISTS (SELECT * FROM sys.database_principals WHERE name = N'login_name')
DROP USER [login_name]
GO
CREATE USER [login_name] FOR LOGIN [login_name]
GO
EXEC sp_addrolemember N'My_StoredProcs', N'login_name'
GO
EXEC sp_addrolemember N'db_datareader', N'login_name'
GO
EXEC sp_addrolemember N'db_datawriter', N'login_name'
GO
You can see that the logins were also members of certain roles, so those will have to be scripted too. Of course, you should only add the roles that belong to each user...! You can inspect which roles a user is assigned by opening SSMS and drilling down to the user under the DB | Security | Users | right-click user | Properties.
您可以看到，登录也是某些角色的成员，所以这些也必须用脚本编写。当然，您应该只添加属于每个用户的角色…!通过打开SSMS并深入到DB |安全|用户|右键单击用户|属性，可以检查用户分配的角色。
Edit
BTW - If your new server doesn't have the users that exist in your old database, you have to create them in master before you drop and re-add them to your restored database.
顺便说一句，如果您的新服务器没有旧数据库中存在的用户，您必须在删除并重新添加到恢复的数据库之前在master中创建它们。
#2
1
IF you have such a tool, most DB compare utilities can be configured to migrate users, role membership, and/or permissions. I know the schema compare tool that is part of visual studio database edition (formerly Data Dude) can do it, I am pretty sure the red gate tools can do it as well.
如果您有这样的工具，大多数DB compare实用程序可以配置为迁移用户、角色成员和/或权限。我知道visual studio数据库版(以前是Data Dude)的模式比较工具可以做到这一点，我很确定red gate工具也可以做到这一点。
I think I remember something about the VS schema compare utility moving to the BIDS equivalent (I forget what the new name is) in SQL 2012. It is at least worth looking into if you have a lot of objects to sync.
我想我记得在SQL 2012中，VS模式比较实用程序与出价等价(我忘记了新名称是什么)。如果有很多对象需要同步，至少值得研究一下。
#1
4
When you restored your database to a new server, even if the same users exist in master, they don't have the same system ids, so you have to drop the users from your restored database and add them to your restored database again. This way, the authenticated user's system ids flow through from master to your database and the rights are all restored without having to re-script all the rights...
当您将数据库还原到新服务器时，即使在master中存在相同的用户，它们也没有相同的系统id，因此您必须将用户从恢复的数据库中删除，并将其再次添加到恢复的数据库中。通过这种方式，经过身份验证的用户的系统id从主服务器流到数据库，所有权限都被恢复，而无需重新编写所有权限……
For instance, when I restored my db to another server, I had to execute the following script for each login that we use:
例如，当我将我的数据库恢复到另一个服务器时，我必须为我们使用的每个登录执行以下脚本:
USE [new_db_name]
GO
IF EXISTS (SELECT * FROM sys.database_principals WHERE name = N'login_name')
DROP USER [login_name]
GO
CREATE USER [login_name] FOR LOGIN [login_name]
GO
EXEC sp_addrolemember N'My_StoredProcs', N'login_name'
GO
EXEC sp_addrolemember N'db_datareader', N'login_name'
GO
EXEC sp_addrolemember N'db_datawriter', N'login_name'
GO
You can see that the logins were also members of certain roles, so those will have to be scripted too. Of course, you should only add the roles that belong to each user...! You can inspect which roles a user is assigned by opening SSMS and drilling down to the user under the DB | Security | Users | right-click user | Properties.
您可以看到，登录也是某些角色的成员，所以这些也必须用脚本编写。当然，您应该只添加属于每个用户的角色…!通过打开SSMS并深入到DB |安全|用户|右键单击用户|属性，可以检查用户分配的角色。
Edit
BTW - If your new server doesn't have the users that exist in your old database, you have to create them in master before you drop and re-add them to your restored database.
顺便说一句，如果您的新服务器没有旧数据库中存在的用户，您必须在删除并重新添加到恢复的数据库之前在master中创建它们。
#2
1
IF you have such a tool, most DB compare utilities can be configured to migrate users, role membership, and/or permissions. I know the schema compare tool that is part of visual studio database edition (formerly Data Dude) can do it, I am pretty sure the red gate tools can do it as well.
如果您有这样的工具，大多数DB compare实用程序可以配置为迁移用户、角色成员和/或权限。我知道visual studio数据库版(以前是Data Dude)的模式比较工具可以做到这一点，我很确定red gate工具也可以做到这一点。
I think I remember something about the VS schema compare utility moving to the BIDS equivalent (I forget what the new name is) in SQL 2012. It is at least worth looking into if you have a lot of objects to sync.
我想我记得在SQL 2012中，VS模式比较实用程序与出价等价(我忘记了新名称是什么)。如果有很多对象需要同步，至少值得研究一下。