MS SQL Server - Copy database roles between databases sql server 2005

Author

Copy database roles between databases sql server 2005

manganb@gmail.com

2006-10-24, 6:33 pm

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(objectprop
erty ( 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...ef_m_s_5e2a.asp
select @ScriptType = 2 -- SQLDMOScript_ObjectP
ermissions
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.

Arnie Rowland

2006-10-24, 6:33 pm

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(objectprop
erty ( 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...ef_m_s_5e2a.asp
> select @ScriptType = 2 -- SQLDMOScript_ObjectP
ermissions
> 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.
>

Sponsored Links