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.
>

copy a db, identity problem

hi
i need to copy a db to another db every single table to another similar to
the first one but with some difference. When i try to do that i need to
remove some constraint and all the identity on the new db. i'd like do that
with a script, i tried:
CREATE TABLE (
id int IDENTITY (1,1) NOT NULL
)
ALTER TABLE prova ALTER COLUMN id int null
GO
it doesnt work.
how can i remove identity from a column of my table'
tx
carloYou can't directly remove the IDENTITY property. You would have to
create a new column or new version of the table.
However, you can use the SET IDENTITY_INSERT option to allow you to
populate the column without removing the IDENTITY property. See Books
Online for details.
David Portas
SQL Server MVP
--|||>> how can i remove identity from a column of my table'
You cannot do this directly. The alternative is to create another table,
copy the data & rename it. The EM interface can get this done with a few
mouse clicks and it may perform reasonably for small-medium sized tables.
Anith|||You don't need to. And, actually, you shouldn't. Look up SET IDENTITY_INSERT
in Books Online. And then use it.
It helps you achieve exactly what you need (judging from your post).
ML

Converting SQL Server Data into XML

Hi Folks!

I have the following sql script I wrote using Explicit Option to convert data from SQL Server tables into a single XML file. I am aware of the tedious nature of the select statements, but this seems to the only option I have to depict parent-child nature of the data in XML format and also to schedule it as a job to run via SQL Server Agent.

My problem is that as I run this using the following command, I get "There is insufficient system memory to run this query" error.
I am using the following commnd:
exec master..xp_cmdshell 'bcp "EXEC swr_cv2..sproc_BuildXMLTree" queryout "C:\test.xml" -U -P -c -r -t'

Is there any way I can tune my query to fix that error?

Thanks so much for your help!

-ParulHi Folks!

I am aware of the tedious nature of the select statements,
-Parul

Really tedious Parul,For the first time I am seeing so big a proc... rather a Mamoth ;)|||I know, that's because the XML feed layout is huge as well...is there a way to tune a FOR XML EXPLICIT query?|||I know, that's because the XML feed layout is huge as well...is there a way to tune a FOR XML EXPLICIT query?
You can increase the max server memory,but I am not sure whether that will help you...check this (http://sqljunkies.com/PrintContent.aspx?type=tutorial&id=0D4FF40A-695C-4327-A41B-F9F2FE2D58F6)|||Thanks! I am really hoping not to tamper with the server settings as it is a production database. I am hoping I can may be build a temp table around this data, any ideas?|||Thanks! I am really hoping not to tamper with the server settings as it is a production database. I am hoping I can may be build a temp table around this data, any ideas?
have you tried XPath queries in this case ?|||No, I am not sure if I can write XPath Queries and schedule them via SQL Server Agent. Do you know if that is possible? Or do I need a .net application for it?|||No, I am not sure if I can write XPath Queries and schedule them via SQL Server Agent. Do you know if that is possible? Or do I need a .net application for it?
check this (http://www.aspfree.com/c/a/MS-SQL-Server/XML-and-the-SQL-2000-Server-part-3-XML-Data-with-XPath-Queries/1/) ...|||Thanks! This seems helpful in generating an XML layout; however, I am still wondering how I can schedule this to run daily...|||Thanks! This seems helpful in generating an XML layout; however, I am still wondering how I can schedule this to run daily...
An easy option can be Altova xmlspy 2006.
Try this www.altova.com (http://www.altova.com) and download XMLSPY 2006|||Thanks! BTW, I got my sql query to work! I replaced all the unions with inserts - now I am inserting all the data into an actual physical data, and then running FOR XML EXPLICIT on it. It seems to be working fine now!

I had a question though - do you know if there is a sql command to merge two files. Basically, I want to merge the header for the xml file with actual xml data in another file - is that possible without running any DOS commands?

Thanks so much!