Copy database roles between databases sql server 2005

Hi There,
This is vexing to say the least.
Setup:
Single SQL 2005 standard server: 2 databases, with the same table
structure and query structure.
Table Count: 78
Query Count: 3
Database A has 8 customized security roles, with different permissions
across all of the tables.
Database B has the same table structure, but different data, and none
of the roles defined in A.
How do I copy the roles from Database A to Database B?
To do it by hand would be error prone, and not much fun...
Someone posted the following in another forum, but I can't get it to
work, says that there is an invalid connection:
- Execute batch in the old DB
-- Execute result in new DB
-- Script permissions on all tables
-- Author: Th. Fuchs, IMC GmbH Chemnitz
declare @.object int, @.hresult int, @.property varchar(255), @.return
varchar(8000)
declare @.src varchar(255), @.desc varchar(255), @.cmd varchar(300)
declare @.ScriptType integer, @.tabname varchar(200), @.dbname
varchar(128), @.pwd varchar(20)
declare @.tablelist table (tabid integer, tabname varchar(128))
set @.dbname = 'INVEKOS2' -- define db to script
set @.pwd = '' -- top secret!
-- Create the sqlserver-object
execute @.hresult = sp_OACreate 'SQLDMO.SQLServer', @.object output
if @.hresult = 0 -- connect to server
execute @.hresult = sp_OAMethod @.object, 'Connect', NULL,
'SMUL-DB-121', 'sa', @.pwd
-- Get all tablenames
insert into @.tablelist(tabid, tabname)
select id, user_name(objectproperty ( id , 'OwnerId')) + '.' +
object_name(id)
from dbo.sysobjects
where objectproperty(id, 'IsTable') = 1
and objectproperty(id, 'IsSystemTable') = 0
-- step through tables, script descriped in
http://msdn.microsoft.com/library/de...f_m_s_5e2a.asp
select @.ScriptType = 2 -- SQLDMOScript_ObjectPermissions
declare cur_tab CURSOR LOCAL FORWARD_ONLY READ_ONLY STATIC for
select tabname from @.tablelist order by tabid
open cur_tab
fetch next from cur_tab into @.tabname
while @.@.fetch_status = 0 and @.hresult = 0
begin
select @.cmd = 'databases("' + @.dbname + '").tables("' + @.tabname +
'").script'
execute @.hresult = sp_OAMethod @.object, @.cmd, @.return OUTPUT,
@.ScriptType
print @.return
fetch next from cur_tab into @.tabname
end
close cur_tab
deallocate cur_tab
-- Destroy the object.
if @.hresult = 0 -- disconnect and freemem
execute @.hresult = sp_OADestroy @.object
-- If Error occurs, get a tip
if @.hresult != 0
begin
execute sp_OAGetErrorInfo @.object, @.src OUT, @.desc OUT
select hresult = convert(varbinary(4),@.hresult), Source = @.src,
Description = @.desc
end
Thanks for your time.
Perhaps one of these articles will give you the information you desire.
http://www.sqlservercentral.com/scri...tions/1598.asp Script Roles
and Permissions
http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
Passwords Between SQL Servers
http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after a
Restore
http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to users
http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
Errors After Restoring Dump
http://support.microsoft.com/kb/274188 Troubleshooting Orphan Logins
http://www.support.microsoft.com/?id=240872 Resolve Permission
Issues -Database Is Moved Between SQL Servers
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
<manganb@.gmail.com> wrote in message
news:1159562833.316536.262150@.m73g2000cwd.googlegr oups.com...
> Hi There,
> This is vexing to say the least.
> Setup:
> Single SQL 2005 standard server: 2 databases, with the same table
> structure and query structure.
> Table Count: 78
> Query Count: 3
>
> Database A has 8 customized security roles, with different permissions
> across all of the tables.
> Database B has the same table structure, but different data, and none
> of the roles defined in A.
> How do I copy the roles from Database A to Database B?
> To do it by hand would be error prone, and not much fun...
> Someone posted the following in another forum, but I can't get it to
> work, says that there is an invalid connection:
> - Execute batch in the old DB
> -- Execute result in new DB
> -- Script permissions on all tables
> -- Author: Th. Fuchs, IMC GmbH Chemnitz
> declare @.object int, @.hresult int, @.property varchar(255), @.return
> varchar(8000)
> declare @.src varchar(255), @.desc varchar(255), @.cmd varchar(300)
> declare @.ScriptType integer, @.tabname varchar(200), @.dbname
> varchar(128), @.pwd varchar(20)
> declare @.tablelist table (tabid integer, tabname varchar(128))
> set @.dbname = 'INVEKOS2' -- define db to script
> set @.pwd = '' -- top secret!
> -- Create the sqlserver-object
> execute @.hresult = sp_OACreate 'SQLDMO.SQLServer', @.object output
> if @.hresult = 0 -- connect to server
> execute @.hresult = sp_OAMethod @.object, 'Connect', NULL,
> 'SMUL-DB-121', 'sa', @.pwd
> -- Get all tablenames
> insert into @.tablelist(tabid, tabname)
> select id, user_name(objectproperty ( id , 'OwnerId')) + '.' +
> object_name(id)
> from dbo.sysobjects
> where objectproperty(id, 'IsTable') = 1
> and objectproperty(id, 'IsSystemTable') = 0
> -- step through tables, script descriped in
> --
> http://msdn.microsoft.com/library/de...f_m_s_5e2a.asp
> select @.ScriptType = 2 -- SQLDMOScript_ObjectPermissions
> declare cur_tab CURSOR LOCAL FORWARD_ONLY READ_ONLY STATIC for
> select tabname from @.tablelist order by tabid
> open cur_tab
> fetch next from cur_tab into @.tabname
> while @.@.fetch_status = 0 and @.hresult = 0
> begin
> select @.cmd = 'databases("' + @.dbname + '").tables("' + @.tabname +
> '").script'
> execute @.hresult = sp_OAMethod @.object, @.cmd, @.return OUTPUT,
> @.ScriptType
> print @.return
> fetch next from cur_tab into @.tabname
> end
> close cur_tab
> deallocate cur_tab
> -- Destroy the object.
> if @.hresult = 0 -- disconnect and freemem
> execute @.hresult = sp_OADestroy @.object
> -- If Error occurs, get a tip
> if @.hresult != 0
> begin
> execute sp_OAGetErrorInfo @.object, @.src OUT, @.desc OUT
> select hresult = convert(varbinary(4),@.hresult), Source = @.src,
> Description = @.desc
> end
> Thanks for your time.
>

Copy database roles between databases sql server 2005

Hi There,
This is vexing to say the least.
Setup:
Single SQL 2005 standard server: 2 databases, with the same table
structure and query structure.
Table Count: 78
Query Count: 3
Database A has 8 customized security roles, with different permissions
across all of the tables.
Database B has the same table structure, but different data, and none
of the roles defined in A.
How do I copy the roles from Database A to Database B?
To do it by hand would be error prone, and not much fun...
Someone posted the following in another forum, but I can't get it to
work, says that there is an invalid connection:
- Execute batch in the old DB
-- Execute result in new DB
-- Script permissions on all tables
-- Author: Th. Fuchs, IMC GmbH Chemnitz
declare @.object int, @.hresult int, @.property varchar(255), @.return
varchar(8000)
declare @.src varchar(255), @.desc varchar(255), @.cmd varchar(300)
declare @.ScriptType integer, @.tabname varchar(200), @.dbname
varchar(128), @.pwd varchar(20)
declare @.tablelist table (tabid integer, tabname varchar(128))
set @.dbname = 'INVEKOS2' -- define db to script
set @.pwd = '' -- top secret!
-- Create the sqlserver-object
execute @.hresult = sp_OACreate 'SQLDMO.SQLServer', @.object output
if @.hresult = 0 -- connect to server
execute @.hresult = sp_OAMethod @.object, 'Connect', NULL,
'SMUL-DB-121', 'sa', @.pwd
-- Get all tablenames
insert into @.tablelist(tabid, tabname)
select id, user_name(objectproperty ( id , 'OwnerId')) + '.' +
object_name(id)
from dbo.sysobjects
where objectproperty(id, 'IsTable') = 1
and objectproperty(id, 'IsSystemTable') = 0
-- step through tables, script descriped in
--
http://msdn.microsoft.com/library/d...r />
_5e2a.asp
select @.ScriptType = 2 -- SQLDMOScript_ObjectPermissions
declare cur_tab CURSOR LOCAL FORWARD_ONLY READ_ONLY STATIC for
select tabname from @.tablelist order by tabid
open cur_tab
fetch next from cur_tab into @.tabname
while @.@.fetch_status = 0 and @.hresult = 0
begin
select @.cmd = 'databases("' + @.dbname + '").tables("' + @.tabname +
'").script'
execute @.hresult = sp_OAMethod @.object, @.cmd, @.return OUTPUT,
@.ScriptType
print @.return
fetch next from cur_tab into @.tabname
end
close cur_tab
deallocate cur_tab
-- Destroy the object.
if @.hresult = 0 -- disconnect and freemem
execute @.hresult = sp_OADestroy @.object
-- If Error occurs, get a tip
if @.hresult != 0
begin
execute sp_OAGetErrorInfo @.object, @.src OUT, @.desc OUT
select hresult = convert(varbinary(4),@.hresult), Source = @.src,
Description = @.desc
end
Thanks for your time.Perhaps one of these articles will give you the information you desire.
http://www.sqlservercentral.com/scr...utions/1598.asp Script Roles
and Permissions
http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
Passwords Between SQL Servers
http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after a
Restore
http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to users
http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
Errors After Restoring Dump
http://support.microsoft.com/kb/274188 Troubleshooting Orphan Logins
http://www.support.microsoft.com/?id=240872 Resolve Permission
Issues -Database Is Moved Between SQL Servers
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
<manganb@.gmail.com> wrote in message
news:1159562833.316536.262150@.m73g2000cwd.googlegroups.com...
> Hi There,
> This is vexing to say the least.
> Setup:
> Single SQL 2005 standard server: 2 databases, with the same table
> structure and query structure.
> Table Count: 78
> Query Count: 3
>
> Database A has 8 customized security roles, with different permissions
> across all of the tables.
> Database B has the same table structure, but different data, and none
> of the roles defined in A.
> How do I copy the roles from Database A to Database B?
> To do it by hand would be error prone, and not much fun...
> Someone posted the following in another forum, but I can't get it to
> work, says that there is an invalid connection:
> - Execute batch in the old DB
> -- Execute result in new DB
> -- Script permissions on all tables
> -- Author: Th. Fuchs, IMC GmbH Chemnitz
> declare @.object int, @.hresult int, @.property varchar(255), @.return
> varchar(8000)
> declare @.src varchar(255), @.desc varchar(255), @.cmd varchar(300)
> declare @.ScriptType integer, @.tabname varchar(200), @.dbname
> varchar(128), @.pwd varchar(20)
> declare @.tablelist table (tabid integer, tabname varchar(128))
> set @.dbname = 'INVEKOS2' -- define db to script
> set @.pwd = '' -- top secret!
> -- Create the sqlserver-object
> execute @.hresult = sp_OACreate 'SQLDMO.SQLServer', @.object output
> if @.hresult = 0 -- connect to server
> execute @.hresult = sp_OAMethod @.object, 'Connect', NULL,
> 'SMUL-DB-121', 'sa', @.pwd
> -- Get all tablenames
> insert into @.tablelist(tabid, tabname)
> select id, user_name(objectproperty ( id , 'OwnerId')) + '.' +
> object_name(id)
> from dbo.sysobjects
> where objectproperty(id, 'IsTable') = 1
> and objectproperty(id, 'IsSystemTable') = 0
> -- step through tables, script descriped in
> --
> http://msdn.microsoft.com/library/d.../>
_s_5e2a.asp
> select @.ScriptType = 2 -- SQLDMOScript_ObjectPermissions
> declare cur_tab CURSOR LOCAL FORWARD_ONLY READ_ONLY STATIC for
> select tabname from @.tablelist order by tabid
> open cur_tab
> fetch next from cur_tab into @.tabname
> while @.@.fetch_status = 0 and @.hresult = 0
> begin
> select @.cmd = 'databases("' + @.dbname + '").tables("' + @.tabname +
> '").script'
> execute @.hresult = sp_OAMethod @.object, @.cmd, @.return OUTPUT,
> @.ScriptType
> print @.return
> fetch next from cur_tab into @.tabname
> end
> close cur_tab
> deallocate cur_tab
> -- Destroy the object.
> if @.hresult = 0 -- disconnect and freemem
> execute @.hresult = sp_OADestroy @.object
> -- If Error occurs, get a tip
> if @.hresult != 0
> begin
> execute sp_OAGetErrorInfo @.object, @.src OUT, @.desc OUT
> select hresult = convert(varbinary(4),@.hresult), Source = @.src,
> Description = @.desc
> end
> Thanks for your time.
>