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/default.asp?url=/library/en-us/sqldmo/dmoref_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/scripts/contributions/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/default.asp?url=/library/en-us/sqldmo/dmoref_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.
>

Converting RS 2000 Project to RS 2005

I finally got a virtual machine setup with Server 2003, SQL 2005 Feb 2005
CTP, and VS 2005 Feb 2005 CTP. Backed up the database that I used in VS 2003
for my RS 2000 project and restored it in SQL 2005 (no issues with that).
Then I opened up the RS 2000 project in VS 2005 and it brought up a wizard to
convert the solution into VS 2005 format. Once that was complete the layout
view for the report looked fine and all of the datasets were pulling back the
proper data.
Whenever I try to preview or build the solution the following error is
displayed:
Cannot call Invoke or BeginInvoke on a control until the window handle has
been created.
Not sure what this means or what I need to do to fix it. I have verified
that RS 2005 is up and running correctly by running some of the
AdventureWorks samples in the Report Manager.
Hopefully this is not a sign on how difficult it will be to move RS 2000
projects over to RS 2005.I wouldn't worry. You still have an early beta.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"b5lurker" <b5lurker@.discussions.microsoft.com> wrote in message
news:E1C0B26F-912B-458B-8E19-DB87D99A33F6@.microsoft.com...
> I finally got a virtual machine setup with Server 2003, SQL 2005 Feb 2005
> CTP, and VS 2005 Feb 2005 CTP. Backed up the database that I used in VS
2003
> for my RS 2000 project and restored it in SQL 2005 (no issues with that).
> Then I opened up the RS 2000 project in VS 2005 and it brought up a wizard
to
> convert the solution into VS 2005 format. Once that was complete the
layout
> view for the report looked fine and all of the datasets were pulling back
the
> proper data.
> Whenever I try to preview or build the solution the following error is
> displayed:
> Cannot call Invoke or BeginInvoke on a control until the window handle has
> been created.
> Not sure what this means or what I need to do to fix it. I have verified
> that RS 2005 is up and running correctly by running some of the
> AdventureWorks samples in the Report Manager.
> Hopefully this is not a sign on how difficult it will be to move RS 2000
> projects over to RS 2005.|||On Thu, 31 Mar 2005 10:41:10 -0800, "b5lurker"
<b5lurker@.discussions.microsoft.com> wrote:
>I finally got a virtual machine setup with Server 2003, SQL 2005 Feb 2005
>CTP, and VS 2005 Feb 2005 CTP. Backed up the database that I used in VS 2003
>for my RS 2000 project and restored it in SQL 2005 (no issues with that).
>Then I opened up the RS 2000 project in VS 2005 and it brought up a wizard to
>convert the solution into VS 2005 format. Once that was complete the layout
>view for the report looked fine and all of the datasets were pulling back the
>proper data.
>Whenever I try to preview or build the solution the following error is
>displayed:
>Cannot call Invoke or BeginInvoke on a control until the window handle has
>been created.
>Not sure what this means or what I need to do to fix it. I have verified
>that RS 2005 is up and running correctly by running some of the
>AdventureWorks samples in the Report Manager.
>Hopefully this is not a sign on how difficult it will be to move RS 2000
>projects over to RS 2005.
Specific Reporting Services 2005 questions are probably best asked on
the microsoft.private.sqlserver2005.reportingsvcs newsgroup, meantime.
Despite the group's name, it's open to the public.
Andrew Watt
MVP - InfoPath|||Andrew Watt [MVP - InfoPath] wrote:
> Specific Reporting Services 2005 questions are probably best asked on
> the microsoft.private.sqlserver2005.reportingsvcs newsgroup, meantime.
> Despite the group's name, it's open to the public.
Prove the existence of this so-called private newsgroup "open to the public"
that you have a propensity for mentioning by providing an actually link to it.
TIA,
GeoSynch|||http://communities.microsoft.com/newsgroups/default.asp?icp=sqlserver2005&slcid=us
"GeoSynch" wrote:
> Andrew Watt [MVP - InfoPath] wrote:
> > Specific Reporting Services 2005 questions are probably best asked on
> > the microsoft.private.sqlserver2005.reportingsvcs newsgroup, meantime.
> > Despite the group's name, it's open to the public.
> Prove the existence of this so-called private newsgroup "open to the public"
> that you have a propensity for mentioning by providing an actually link to it.
> TIA,
> GeoSynch
>
>|||Thanks. I had searched MSDN and Google for it without luck.
"MJ Taft" <MJTaft@.discussions.microsoft.com> wrote in message
news:30B98187-E4C2-484B-84C7-9A9FA70AECB3@.microsoft.com...
> http://communities.microsoft.com/newsgroups/default.asp?icp=sqlserver2005&slcid=us
> "GeoSynch" wrote:
>> Andrew Watt [MVP - InfoPath] wrote:
>> > Specific Reporting Services 2005 questions are probably best asked on
>> > the microsoft.private.sqlserver2005.reportingsvcs newsgroup, meantime.
>> > Despite the group's name, it's open to the public.
>> Prove the existence of this so-called private newsgroup "open to the public"
>> that you have a propensity for mentioning by providing an actually link to
>> it.
>> TIA,
>> GeoSynch
>>

Converting MSAccess .MDB to SQL Server database

I need to switch from MS Access to SQL Server for my database. To setup a development environment I downloaded the free Microsoft SQL ServerExpress (February CTP version). I installed the required .NET Frameworkv2, and then SQLExpress. The install was done using all the defaults,and was done successfully. I also downloaded and installed the SQLExpress Manager Tool.
The SQL Server was installed on the same machine as my VS.NETdevelopment environment. The SQL Server process is now running, and Ican connect to the server using the SQL Express Manager Tool. Thisallows me to view and query the sample databases, but not much else.
To convert my Access .MDB database to SQL Server, I am trying to usethe MS Access Upsizing Wizard. The version of Access I am using isAccess 2002 on a Windows XP-Professional system. The problem is thatAccess cannot get a connection to the SQL Server. I tried using thedefault server name "(local)" and "Use Trusted Connection", but Ireceive the following error:
Connection failed:
SQLState: '01000'
SQL Server Error: 2
[Microsoft][ODBC SQL Server Driver[]Shared Memory]ConnectionOpen (Connect()).
Connection failed:
SQLState: '08001'
SQL Server Error: 17
[Microsoft][ODBC SQL Server Driver[]Shared Memory]SQL Server does not exist or access denied.
Curiously, I get this exact same error message even if the SQL serviceis stopped. So I'm pretty sure the problem is that it is not findingthe SQL server, and not a security issue.
In order to connect to the SQL server using the SQL Server ManagerTool, you have to provide the actual instance name for the server"COMPNAME\SQLExpress". So I tried using this server name in the AccessUpsizing Wizard, but this returns the same error message as aboveexcept the first SQL Server Error is 53. I also tried using a Login IDand password (using the Windows administrator ID and password, and alsothe "sa" ID and password) to no avail.
I am at wits end, and can't figure out why Access can't find the SQL Server. Any ideas would be appreciated.
Thanks.

I believe I found the answer to my problem through trial and error. Itappears that that Access wants to communicate with the SQL Server viaTCP/IP, however the default for SQL Server Express when installed onthe local machine is shared memory. If you enable shared memory in theSQL Computer Manager, it all seems to work. And it also appears thatthe server name must be specified as "COMPNAME\SQLExpress", not"(local)".|||

Thank you Paul.

After several hours of searching, your solution worked right out of the box.

You would only hope that some of the folks at MSDN would mention your solution on their websites.