Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

System tables Expand / Collapse
Author
Message
Posted Friday, September 21, 2012 3:12 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, November 25, 2013 9:16 PM
Points: 43, Visits: 94
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
Post #1362499
Posted Friday, September 21, 2012 3:17 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 4:10 AM
Points: 5,221, Visits: 5,119
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





Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger

Post #1362501
Posted Friday, September 21, 2012 3:55 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, November 25, 2013 9:16 PM
Points: 43, Visits: 94
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..
Post #1362513
Posted Friday, September 21, 2012 3:59 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 4:10 AM
Points: 5,221, Visits: 5,119
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.



Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger

Post #1362515
Posted Friday, September 21, 2012 4:40 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, November 25, 2013 9:16 PM
Points: 43, Visits: 94
thanks for the reply but still i got only one output still the administrator when i run this sp_help_revlogin
Post #1362541
Posted Friday, September 21, 2012 4:46 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 4:10 AM
Points: 5,221, Visits: 5,119
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.




Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger

Post #1362544
Posted Sunday, September 23, 2012 11:17 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, November 25, 2013 9:16 PM
Points: 43, Visits: 94
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'

Post #1363335
Posted Monday, September 24, 2012 1:53 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 4:10 AM
Points: 5,221, Visits: 5,119
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.




Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger

Post #1363354
Posted Tuesday, September 25, 2012 9:10 AM


Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, July 11, 2013 8:34 AM
Points: 4, Visits: 47
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
Post #1364113
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse