October 31, 2004 at 8:12 am
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
October 31, 2004 at 9:24 am
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.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
November 1, 2004 at 3:22 pm
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.
November 1, 2004 at 4:05 pm
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/
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply