Copy Permissions for a database

CREATE proc copy_permissions_for_database
  @from_user varchar(100), @to_user varchar(100) as
--copies all permissions from @from_user to @to_user
--does not yet support column level permissions
--to delete a user from a database, use this: exec sp_revokedbaccess 'NAME_OF_USER'
--to drop a login from a server, use this: exec sp_droplogin 'NAME_OF_LOGIN'

declare @s varchar(1000)

set nocount on

--if the @from_user login doesn't exist in this database, error and return.
if not exists (select * from dbo.sysusers where name = @from_user)
begin
  print 'ERROR - User "' + @from_user + '" does not exist in this database.'
  return
end

--if the @to_user login doesn't exist on the server, error and return.
if not exists (select * from master..syslogins where name = @to_user)
begin
  print 'ERROR - Login "' + @to_user + '" does not exist on the server.  Run "exec sp_addlogin ''' + @to_user + '''" to add it.'
  return
end

--if the @to_user already exists in this database, error and return.
if exists (select * from dbo.sysusers where name = @to_user)
begin
  print 'ERROR - User "' + @to_user + '" already exists in this database.'
  return
end

exec sp_grantdbaccess @to_user

--copy roles
DECLARE c_from_user_roles CURSOR
READ_ONLY
FOR
select u2.name 
from sysmembers m 
inner join sysusers u1 on m.memberuid = u1.uid 
inner join sysusers u2 on m.groupuid = u2.uid where u1.name = @from_user

DECLARE @role_name varchar(40)
OPEN c_from_user_roles

FETCH NEXT FROM c_from_user_roles INTO @role_name
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
  set @s = 'exec sp_addrolemember ''' + @role_name + ''', ''' + @to_user + ''''
  print @s
  exec(@s)
END
FETCH NEXT FROM c_from_user_roles INTO @role_name
END

CLOSE c_from_user_roles
DEALLOCATE c_from_user_roles

--copy permissions
--put a list of all the current @from_user permissions into a cursor.
--loop through that cursor and set the same permissions for @from_user.
DECLARE c_from_user_permissions CURSOR
READ_ONLY
FOR
select
  object_name = o.name,
  protect_type_name = dbo.f_lookup_system_id('sysprotects_protecttype', p.protecttype),
  action_name = dbo.f_lookup_system_id('sysprotects_action', p.action)
from
  sysprotects p
  inner join sysusers u on p.uid = u.uid
  inner join sysobjects o on p.id = o.id
where
  u.name = @from_user and
  not o.type = 'S' and
  o.name not like 'sync%'
order by object_name

DECLARE @object_name varchar(40)
DECLARE @protect_type_name varchar(40)
DECLARE @action_name varchar(40)
OPEN c_from_user_permissions

FETCH NEXT FROM c_from_user_permissions INTO @object_name, @protect_type_name, @action_name
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
  set @s = @protect_type_name + ' ' + @action_name + ' ON ' + @object_name + ' TO ' + @to_user
  print @s
  exec(@s)
END
FETCH NEXT FROM c_from_user_permissions INTO @object_name, @protect_type_name, @action_name
END

CLOSE c_from_user_permissions
DEALLOCATE c_from_user_permissions

print 'SUCCESS - Permissions have been copied from ' + @from_user + ' to ' + @to_user


GO