Copying users from one database to another

how do i copy all the users, roles, permissions etc from on database to
another database?JT
Not easy task. If you have the same structure of the database the best thing
will be restoring the database with a different name
Otherwise, you will have to generate a script for adding users to be mapped
with the logins
sp_grantdbaccess @.login
sp_addrolemember 'db_owner',@.login
"JT" <col@.newsgroup.nospam> wrote in message
news:ec9iL$pNGHA.2884@.TK2MSFTNGP12.phx.gbl...
> how do i copy all the users, roles, permissions etc from on database to
> another database?

copying users between roles

I would like to copy all users currently from RoleA into RoleB. I know there
must be a nice way to script this. Any ideas?Robert
There is no a "nice" script to move users from one Role to another at least
I am not aware.
You will have to deal with system tables that hold the info about users but
it is not recommended.
"Robert Kinesta" <RobertKinesta@.discussions.microsoft.com> wrote in message
news:66EA482B-0FD9-49A9-B049-6CADCA5B5BD1@.microsoft.com...
>I would like to copy all users currently from RoleA into RoleB. I know
>there
> must be a nice way to script this. Any ideas?|||Hi Robert,
This may help u......a nice script to move all users from roleA to roleB
create table #users (
UserName sysname,
GroupName sysname,
LoginName sysname,
DefDBName sysname,
UserID smallint,
SID smallint
)
insert #users
exec('sp_helpuser')
select identity(int,1,1) as idn, * into #usersInRoleA from #users where
GroupName like 'roleA'
declare @.i as int,
@.maxusers as int,
@.username as sysname
select @.maxusers = max(idn) from #usersInRoleA
set @.i = 1
while (@.i <= @.maxusers)
begin
select @.username = username from #usersInRoleA where idn = @.i
exec sp_addrolemember 'roleB',@.username
exec sp_droprolemember 'roleA',@.username
set @.i = @.i + 1
end
drop table #usersInRoleA
drop table #users
enjoy and keep going...
"Robert Kinesta" wrote:

> I would like to copy all users currently from RoleA into RoleB. I know the
re
> must be a nice way to script this. Any ideas?|||Hi Robert
Maybe something like:
CREATE TABLE #Rolemembers ( DbRole sysname,
MemberName sysname,
MemberSID varbinary(85) )
INSERT INTO #Rolemembers ( DbRole, MemberName, MemberSID )
EXEC sp_helprolemember 'ISDLOAD_ROLE'
DECLARE @.member sysname
DECLARE Member_Cursor CURSOR FOR SELECT MemberName FROM #Rolemembers
OPEN Member_Cursor
FETCH NEXT FROM Member_Cursor INTO @.member
WHILE @.@.FETCH_STATUS = 0
BEGIN
EXEC sp_executesql N'EXEC sp_addrolemember @.rolename= ''ROLEB'' ,
@.membername = @.membernm' ,
N'@.membernm sysname',
@.membernm = @.member
FETCH NEXT FROM Member_Cursor INTO @.member
END
CLOSE Member_Cursor
DEALLOCATE Member_Cursor
DROP TABLE #Rolemembers
John
"Robert Kinesta" wrote:

> I would like to copy all users currently from RoleA into RoleB. I know the
re
> must be a nice way to script this. Any ideas?

Copying a cube but using a different data source

We have a cube that we've been using for a while. I need to make several more cubes with the exact same schema and roles and everything else...just with different data sources. The other data sources have the same schema as the database the original cube is using. I know that when you copy a cube and paste it, the data source is copied over too. And I've read that you can't change the data source of a cube.

My question is what is the easiest way to create the new cubes? Or do I have to create every new cube from scratch (pain in the butt)?

Thanks in advance for your advice.
After you made a copy of your project, create a new DataSource to point to your new relational DB. Open DSV in Xml Mode, change the tag <DataSourceID>...</DataSourceID> to point to your new data source ID.|||Thanks Ken. We're using AS2000. Is there a DSV equivalent?
|||Oh AS2000 is total different story then. There is no DSV concept in AS2000. You can try changing the connection string in the same data source rather than switching the data source on the cube.|||Yeah, I thought about that but we have another cube using the data source so that option is out. Looks like I will need to create the each new cube from scratch. : (
|||Go to every partition of this cube, edit it, and then change the data source. This should do a job.|||Thanks Sasha!

Copying "My Reports" between users.

Is there a way to copy a report saved in a users "My Reports" folder to a different users folder? If so what permissions and roles do I need to be in?Using the default settings no user has enough permissions to do this. You would need each user to give you permission or you need to go in as an admin (local RS box admin) and modify the security permissions to give yourself permission.