Copying user roles and permission from one database to another

Question

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?

We are running the SQL server 2012 version now. but that old db was in SQL Server 2008 r2. is that causing the problem?

Solution 1

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...

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.

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.

Solution 2

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.

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.