System tables

Question

Post reply

System tables

jolan.mahinay

Ten Centuries

Points: 1111
More actions
September 21, 2012 at 3:12 am

#268019

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

Viewing 9 posts - 1 through 8 (of 8 total)

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

Ant-Green SSC Guru Points: 113507 More actions · Answer 1

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

jolan.mahinay Ten Centuries Points: 1111 More actions · Answer 2

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

Ant-Green SSC Guru Points: 113507 More actions · Answer 3

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.

jolan.mahinay Ten Centuries Points: 1111 More actions · Answer 4

thanks for the reply but still i got only one output still the administrator when i run this sp_help_revlogin

Ant-Green SSC Guru Points: 113507 More actions · Answer 5

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.

jolan.mahinay Ten Centuries Points: 1111 More actions · Answer 6

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'

Ant-Green SSC Guru Points: 113507 More actions · Answer 7

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.

georgesquared SSC Journeyman Points: 80 More actions · Answer 8

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