Aroor Aroor - 3 months ago 13
SQL Question

Copying user roles and permission from one database to another

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?


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]
IF  EXISTS (SELECT * FROM sys.database_principals WHERE name = N'login_name')
DROP USER [login_name]
CREATE USER [login_name] FOR LOGIN [login_name]
EXEC sp_addrolemember N'My_StoredProcs', N'login_name'
EXEC sp_addrolemember N'db_datareader', N'login_name'
EXEC sp_addrolemember N'db_datawriter', N'login_name'

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.


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.