copying roles and user to another server

Question

copying roles and user to another server

lior gal

SSC Journeyman

Points: 94
More actions
October 31, 2004 at 8:12 am

#163055

HI
Is there a way to copy users and roles from one server to another, something like the generate script option for SP?
I have an access control configure on my p.c but now i have to transfer it to my client server and I dont want to built it all over.

thanks

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 1

You can script it out and run the script on the other server or try the Copy Objects Task in DTS. Select only users, roles and permissions. The objects have to be named the same for this to work.

mlstewart67 SSC Rookie Points: 26 More actions · Answer 2

http://support.microsoft.com/default.aspx?scid=kb;en-us;246133&Product=sql2k

Microsoft Support Article ID

:

246133

This article on the Microsoft support site provides you with the stored procedure sp_help_revlogin. This will transer SQL Server logins from one server to the other and retain the SIDs and passwords.

One pitfall to watch out for...... The source and the destination sql server installs must have the same sort order. (sp_helpsort to see the sort order). If one server has a binary sort order and the other has a dictionary sort order you will run into problems retaining the passwords. This information is covered in the article. I would advise anyone to read the entire article before starting.

I use this stored procedure to regularly backup the logins for each of my sql servers just in case I'm not able to restore a master database.

colin.Leversuch-Roberts SSC Guru Points: 52643 More actions · Answer 3

you can simply replace the contents of sysxlogins and sysusers - I usually make backups of these tables as part of my DR stuff anyway.

I also have a proc to script users which generates a series of sql scripts to add users - it doesn't create roles - I use it to migrate users in test environments and so on

script below

CREATE proc sp_test22

-- =============================================================

-- Procedure: sp_test22 still under development

-- Written by: Colin Leversuch-Roberts

--

-- Purpose: create script to add current users to a database

--

-- System: DBA maintenance - MUST be in master MUST start with sp_

--

-- Input Paramters:

--

-- Returns :

--

-- Usage: call this from any user database to create a list of grant access and addrole commands

-- paste into QA the result set

--

-- Notes:

--

-- VERSION HISTORY

-- Version No Date Description

-- 1 14th may 2004 Initial Release

-- (c) colin leversuch-roberts http://www.kelemconsulting.co.uk 2004

-- =============================================================

as

set nocount on

--

select 'use '+db_name()+char(13)+'go'+char(13)

--

select 'exec.dbo.sp_grantdbaccess '+''''+l.name+''''+','+''''+ l.name+''''+

case

when u.isntgroup = 1 then ' -- NT Group '

else ' '

end

+

case

when u.isntuser = 1 then ' -- NT User '

else ' -- SQL User '

end

from dbo.sysusers u join master.dbo.syslogins l on l.sid=u.sid

where uid>2 and u.sid is not null

print '-- ================================================================================'

--

select 'use '+db_name()+char(13)+'go'+char(13)

--

select 'exec.dbo.sp_addrolemember '+''''+(select o.name collate latin1_general_cs_as from dbo.sysusers o where o.uid = m.groupuid )+''''+','+''''+ l.name+''''

from dbo.sysusers u join dbo.sysmembers m on u.uid=m.memberuid

join master.dbo.sysxlogins l on u.sid= l.sid

where uid>2

GO

[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/