September 21, 2012 at 3:12 am
hello to all,
i would like to ask if ever there are ways to copy the data from system tables which is sysusers, here's the scenario i have sqlserver 2000 enterprise and have tables and system tables, now i created another database to replicate those tables and data but i already done this but my problem is my client wants to export those 300 sql users to the replicated database which is found at sysusers system table but i was not able to export those data..anyone knows how to export those data?
thanks
September 21, 2012 at 3:17 am
If you want to transfer logins from ServerA to ServerB, there are a number of ways of doing it.
Can use the transfer logins task in DTS or SSIS depending which server you want to run the transfer on.
Lookup a procedure on Google called sp_help_revlogin, which creates a list of users with their passwords and SIDS which can be run on another server to recreate the SQL logins
September 21, 2012 at 3:55 am
do i need to add sql user one by one using the sp_help_revlogin? because when i run this script its generate a script like this EXEC master..sp_grantlogin 'BUILTIN\Administrators' then run successfully but i think this will input one by one..
September 21, 2012 at 3:59 am
If you run the procedure with no username it will generate the script for all logins, then you simply just need to copy and paste the output to a new query window and run it on the other server.
September 21, 2012 at 4:40 am
thanks for the reply but still i got only one output still the administrator when i run this sp_help_revlogin
September 21, 2012 at 4:46 am
Appreciate that this may seem like a silly question, but you are running the procedure on the SQL 2000 server not the new server
Also you are running this version of sp_help_revlogin http://support.microsoft.com/kb/246133, not this one http://support.microsoft.com/kb/918992
There is a version for SQL 7 and 2000, and one for 2005 onwards.
September 23, 2012 at 11:17 pm
i tried to run this EXEC master..sp_help_revlogin from the source but still the output is only one..
/* sp_help_revlogin script
** Generated Sep 24 2012 1:11PM on JHOLS\SQL2000 */
/***** CREATE LOGINS *****/
-- Login: BUILTIN\Administrators
IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE [name] = 'BUILTIN\Administrators')
CREATE LOGIN [BUILTIN\Administrators] FROM WINDOWS
/***** SET DEFAULT DATABASES *****/
-- Login: BUILTIN\Administrators
ALTER LOGIN [BUILTIN\Administrators] WITH DEFAULT_DATABASE=[master]
/***** SET SERVER ROLES *****/
-- Login: BUILTIN\Administrators
exec master.dbo.sp_addsrvrolemember @loginame='BUILTIN\Administrators', @rolename='sysadmin'
September 24, 2012 at 1:53 am
In that case there must only be that one login on the server.
Can you attach a screen shot of the security --> logins area in Enterprise Manager.
September 25, 2012 at 9:10 am
No, if you're only getting one login, you're not running it as SA. You need the rights to read the logins from the source server and the rights to create logins on the destination side. Make sure you have SA rights. DBO is NOT sufficient.
I always review the output of sp_helprevlogin because it DOES list ALL logins, including already built-in logins such as SA. I like to comment out the ones I don't need so I don't get the spurious errors.
Once you run sp_helprevlogin, you shouldn't need to do anything else. Your user databases and their sysusers tables all work off SID. the nice thing about sp_helprevlogin is it reproduced the IDs exactly, including the SID & password, so all the user access should automagically reappear. It's a really cool tool.
Speaking of spurious errors, make sure you examine the output carefully. Some logins will invariably fail. The most likely cause is the SID is already taken, or the login name already exists, but with the wrong ID. You'll need to manually fix them, most likely by dropping the user login and maybe from each database and then recreating it manually. You can use the sp_helprevlogin code as a template to recreate the adjusted user ID. So, for example, you can recreate it with the same password.
One last caveat: sp_helprevlogin does not handle roles. So if you are moving DBA logins with SA rights, they will appear on the destination with no SA rights. You'll need to manually add that in yourself. You can massage sp_helprevlogin to handle roles, if you wish (I need to do that one day!)
Good luck,
G**2
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply