Copy SQL 2008 Database permissions from one Database to Another

So recently I had to replicate permissions from one Database to another.  This can be quite tedious, especially when there are lots of individual user permissions that have been applied to the Database.  After some hunting I found a script that accomplishes exactly this as follows:

—————————————————————
— COPY USERS & ROLES
— from 1 db on this server to another db on this server
—————————————————————
———————–
— change all AdventureWorks2012 to “from” DataBase (OLD db)
— change all TestPermissions to “to” DataBase (NEW db)

— If the user had no permissions to DB, this will copy that from the “from” db and
— print a msg in grid ‘ADDED USER ‘, @user_name

— All Roles on the “from” db will be applied to the “to” db

——————————————————-
— based on code from http://www.sqlservercentral.com/scripts/T-SQL+Aids/30754/
——————————————————-

— Copy Roles
USE TestPermissions

declare @s varchar(1000)
DECLARE c_from_user_roles CURSOR
READ_ONLY
FOR
select u.name as UserName, a_ROLE.name as RoleName –, *
from AdventureWorks2012.dbo.sysusers as u
join AdventureWorks2012.dbo.sysmembers AS Person
on u.uid = Person.memberuid
JOIN AdventureWorks2012.dbo.sysusers AS a_ROLE
on a_ROLE.uid = groupuid
WHERE U.NAME + A_ROLE.NAME
NOT IN (SELECT U.NAME + A_ROLE.NAME
from TestPermissions.dbo.sysusers as u
join TestPermissions.dbo.sysmembers AS Person
on u.uid = Person.memberuid
JOIN TestPermissions.dbo.sysusers AS a_ROLE
on a_ROLE.uid = groupuid)
— AND U.NAME = ‘jduffy’ — TEST only !

DECLARE @role_name varchar(40)
DECLARE @user_name varchar(40)
OPEN c_from_user_roles

FETCH NEXT FROM c_from_user_roles INTO @user_name, @role_name
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
BEGIN
if NOT exists (select * from TestPermissions.dbo.sysusers where name = @user_name)
begin
exec sp_grantdbaccess @user_name — add user to DB
— print ‘ADDED USER ‘ @user_name
SELECT ‘ADDED USER ‘, @user_name
end
END
BEGIN
set @s = ‘exec sp_addrolemember ”’ + @role_name + ”’, ”’ + @user_name + ””
print @s
exec(@s) — copy Role for user
END
END
FETCH NEXT FROM c_from_user_roles INTO @user_name, @role_name
END

CLOSE c_from_user_roles
DEALLOCATE c_from_user_roles

 
Comments

No comments yet.